MSCI261 macro for OpenOffice Calc and Microsoft Excel
One of the assignments for MSCI 261 (engineering economics) requires spreadsheets. However, the financial formulae in Excel and OpenOffice.org are slightly different from the formulae we use in the course. This was slightly annoying, so I decided to define some of my own functions for OpenOffice.
(This post assumes basic knowledge of Excel/OpenOffice functions, and MSCI261.)
Here is the code:
REM ***** BASIC ***** Option Explicit Sub Main End Sub Function FP(P, i, N) REM Compound Amount (F/P) Factor FP = P * (1 + i)^N End Function Function PF(F, i, N) REM Present Worth (P/F) Factor PF = F / (1 + i)^N End Function Function AF(F, i, N) REM Sinking Fund (A/F) Factor AF = (F * i) / ((1 + i)^N - 1) End Function Function FA(A, i, N) REM Uniform Series Compound Amount (F/A) Factor FA = A * (((1 + i)^N - 1) / i) End Function Function AP(P, i, N) REM Capital Recovery (A/P) Factor AP = (P * i * (1 + i)^N) / ((1 + i)^N - 1) End Function Function PA(A, i, N) REM Series Present Worth (P/A) Factor PA = A * (((1 + i)^N - 1) / (i * (1 + i)^N)) End Function Function AG(A, G, i, N) REM Arithmetic Gradient to Annuity Conversion (A/G) Factor REM Returns A_tot AG = A + G*((1 / i) - (N / ((1 + i)^N - 1))) End Function Function PAg(A, g, i, N) REM Geometric Gradient Series to Present Worth Conversion Factor Dim i_o As Double i_o = ((1 + i) / (1 + g)) - 1 PAg = A * ((((1 + i_o)^N - 1) / (i_o * (1 + i_o)^N)) / (1 + g)) End Function
This is how to get these functions working in OpenOffice:
- Copy the above code.
- Go to Tools > Macros > Organize Macros > OpenOffice.org Basic
- In the “Macro from” field, select the name of your document and click New.
- You can leave the module name as “Module1” or call it something like “MsciFunctions.” Click OK.
- In the BASIC-IDE window that comes up, delete everything and paste the above code.
- Close it, and you’re done!
All of the functions except the Arithmetic and Geometric Gradients have only three arguments. The first is the amount of money you’re multiplying the factor by (eg for the A/P factor, the first argument is P), the second is the interest rate (as a decimal, so 10% would be entered as 0.1), and the third is the number of periods.
The Arithmetic Gradient factor looks like this: (A, G, i, N) Where A is the original amount, G is the (constant) amount it grows, i is the interest rate, and N is the number of periods. The function returns a single, constant annuity.
Similarly, the Geometric Gradient looks like this: (A, g, i, N) g is the percent A grows by each period. This function returns the present worth of the entire geometric gradient series.
Anyway, this quickly-written post should be enough to get you going on the assignment. If not, just leave a comment or send me an email. Also, I’d like to hear from anyone who attempts this with Excel and gets it working.
If you’re a bit lazier and don’t mind enabling macros in OpenOffice, you can download this spreadsheet. Under default settings, when you open it, it should say something about macros being disabled. To enable them, go to Tools > Options > Security > Macro security and select Medium. Then close the file and reopen it. This time it’ll ask if you want to enable or disable macros. If you trust me, then you can click Enable. (If not, then just follow the above instructions to manually insert the macro.)
Chris Kleynhans has provided a Microsoft Excel version of the spreadsheet. Once again, you’ll have to enable the macro — there should be a little bar that appears, where you can click on Options.