How can I solve financial problems using Microsoft Excel?
From FinancialPlanning
Excel has a bunch of built-in functions that handle both simple and more complicated financial calculations. Each uses terminology similar to that of a financial calculator...the most useful ones are:
- PV(rate, nper, pmt, fv, type)
- FV(rate, nper, pmt, pv, type)
- PMT(rate, nper, pv, fv, type)
- RATE(nper, pmt, pv, fv, type, guess)
- XIRR(values, dates, guess)
All of these functions have detailed descriptions in the Excel Help files. You may need to load the Analysis Toolpak add-in if these functions aren't available when you try them (you'll see #NAME in the cell as an error message).
XIRR is especially useful because it allows you to calculate a the internal rate of return on a series of cash flows that don't occur at regular intervals (as is required when using annuity calculations like the FV function above). For example if you deposit $10,000 into an account, add $4,000 eight months later, take out $500 seven months later, and then end up with $15,266 at the end of the second year...well you can see how it would be hard to figure out what rate of return you earned on your money. The FV formula only deals with a series of evenly spaced deposits (or withdrawals). XIRR could solve that problem; you'd key in the dates for each of those cash flows in one column, and the associated cash flows in the next column, making sure your sign convention is correct (money you receive is positive, money you deposit is negative). When you enter those cell ranges in the =XIRR() function you'll solve for the rate of return. Most financial calculators aren't able to do this so it's one area where Excel is especially useful.
