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.

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

February 25, 2010

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.