Ming-Ho's Blog

# MSCI261 macro for OpenOffice Calc and Microsoft Excel

February 23, 2010

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:

1. Copy the above code.
2. Go to Tools > Macros > Organize Macros > OpenOffice.org Basic
3. In the “Macro from” field, select the name of your document and click New.
4. You can leave the module name as “Module1” or call it something like “MsciFunctions.” Click OK.
5. In the BASIC-IDE window that comes up, delete everything and paste the above code.
6. 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.