Finance Math
Excel is ideal for constructing balance sheets and amortization schedules, not to mention a host of other financial tools and reports. However, those are fairly straightforward applications of concepts already discussed. What may not be so obvious, but is worth pointing out, is that Excel also has some of the more important financial formulas built right in. An overview of the present value and future value functions will be given here, assuming you are already acquainted with concepts behind them.
Present Value
Determining
the current worth of a series of future payments is the basis behind most
calculations involving loans (as well as other things). Excel has a function
PV( ) especially tailored for this purpose. It assumes that you know the
periodic interest rate (annual rate divided by the number of payments
per year), the total number of payments and the amount of each payment.
By default it assumes that you want to pay off the loan completely in
that number of payments and that payments are made at the end of each
interest compounding period rather than the beginning, but those provisions
can be changed. Bear in mind that for the result to be positive, the payment
amount must be negative.
Technically speaking, with the help of Goalseek, you can work this function backwards to calculate the rate, number of payments or the payment amount that corresponds to a given present value. However this isn't necessary, because there are some additional built-in functions for just that purpose.
Going backwards - RATE( ), NPER( ), and PMT( )
You can calculate each of these quantities if you know the present value (or future value as it turns out) and all the other quantities. While the hand calculation for PMT is rather straightforward, finding NPER would involve some logarithms, and RATE is actually a nonlinear equation that can't be solved algebraically by hand. As indicated, if you are interested in investing regular payments toward some future goal (FV), the same functions can be used to determine the number of payments, or the amount of payment, or (less practically) the required interest rate.
Future Value
If
you know how large a payment you are setting aside (such as the portion
of your paycheck taken out for a retirement plan) for a future goal, then
FV( ) is the function for you. The arguments are the same (except for
PV, the current balance if you are doing the calculation sometime after
the starting point) as for the present value calculation and once more,
the payment amount must be negative if you want to get a positive future
value.
There really isn't much more to be said regarding these functions. There are numerous other functions included in the Financial category - some of which are just variations on the same theme. It is worth glancing over these to see if there is anything that would be useful.
 ...Previous
Page |
Contact Leo Wibberly at ldwibber@vcu.edu or (804) 740-4650 to make appointments