No BS Finance

Excel is an invaluable tool when trying to model your financial future. Online tools or those found in Quicken or Microsoft Money are nice, but have limited flexibility. Excel allows for creating simple or complex models. Sometimes equations are built in; other times you create what you need.

For example, do you know how much $10,000 today will be if you earn 8% for the next 25 years? There are several ways to come up with the right answer:

1. The long way - start with the $10,000 and do a lot of multiplication. After one year you have ($10,000 * 1.08) or $10,800. After year 2 you have ($10,800 * 1.08) or $11,664. And so on…feel free to multiply it out, but the answer is $68,484.75.
2. Shortcut - after doing the above you realize that you can do one problem instead of 25. The shortcut is ($10,000 * (1.08 ^ 25)). ^ 25 means “raised to the 25th power. So, the quick answer is the same as before, $68,484.75.
3. Use a simple Excel formula - The formula is ‘=FV(0.08,25,0,-10000)’ (don’t include the single quotes) which also results in $68,484.75.

Methods 2 and 3 are both equally easy for this simple case, but as your desired calculations get more complex, Excel becomes more of a boon. What would you need to do if the rate of return was 8% for the first 10 years, but 9% for the last fifteen? While methods 1 and 2 can be modified to get the right answer, using Excel will allow you to quickly change your inputs and see the impact (what if you end up only having $8,500 to invest…and so forth).
I’ll periodically discuss how to use Excel to model financial equations.

One thing to note - outflows are negative values and inflows are positive. If you look closely at the Excel formula above, you’ll notice the starting value of $10,000 is a negative. We’re “giving up” that amount to savings today and will get it back in the future. So the $10,000 is an outflow (negative) and the total at the end will be an inflow (positive).

Update: More calculations and an Excel spreadsheet to download are also available.