# 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.

## Updates

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.