Hi Steve!
Loans are only one use of the FV function.
FV returns the accumulated amount of any PV and any regular series of
equal PMT.
To find out how much you have paid out on a loan, you can use the
Analysis ToolPak CUMPRINC although you can use the FV function
Take base information of a loan of 100000 at an APR12 of 6% over 10
years making monthly repayments with the first due 1 month after draw
down.
=PMT(6%/12,120,100000,0,0)
Returns: -1110.20501941652
The positive receipt of the loan results in a negative payment out in
the form of loan repayments.
You can verbalise the PMT function, "What payments are needed using a
rate of 6%/12 per period over 120 periods where I have an immediate
receipt of 100000 and at the end of the period I owe nothing. (Final
argument of 0 indicates that payments are made in arrears - first
payment one period after draw down)."
Using the FV function I can find the amount I owe or am due at a
future date:
=FV(6%/12,24,-1110.20501941652,100000,0)
Returns: -84481.2932583182
The negative indicates that there is still an obligation to pay
84481.21 after 24 months. This means that 24 months down the track I
have paid off 15518.71
Again, this can be verbalised, "What is the future right or obligation
where I receive 100000 (PV) now and make regular payments of
1110.20501941652 per period for 24 periods where the rate is 6%/12 per
period and the payments are in arrears (i.e first is made...).
But look at what is happening in that FV function:
The loan accumulates to:
=FV(6%/12,24,0,100000,0)
Returns: -112715.977620539
"If I receive 100000 now what will be the obligation in 24 periods
time using a rate of 6%/12 per period and assuming no repayments?"
The repayments accumulate to:
=FV(6%/12,24,-1110.20501941652,0,0)
Returns: 28234.6843622206
"How much will I accumulate if for 24 periods I make payments of
1110.20501941652 per period using a rate of 6%/12 per period with the
first payment made after 1 period. I have nothing in the account at
present"
Adding together
=-112715.977620539+28234.6843622206
Returns: -84481.2932583182
Rule 1 with Excel financial functions: Who am I? borrower or lender?
Rule 2 does any known amount come towards me (+) or go away from me?
Rule 3 interest rate used must be the rate per period of time counted
by nper and payments must be payments per period counted by nper.
Re your deposit of 1500. Remember that there are two elements to the
car purchase. The car transaction and the loan transaction. The car
transaction is facilitated by payments by you of 1500 and payment by
the lender of 100000. The loan calculation is done in isolation and is
based upon the amount of loan, term , rate and type of repayment.
In more complex cases where there is an interest only element in the
loan there is a terminal obligation to repay the interest only
carrying element (known as the balloon payment). That's why both PV
and PMT functions have room for a FV argument.
You might find time value of money diagrams useful here. Take a look
at maths of finance texts or in an Excel context you can look at
Chapters 11-13 of John Walkenbach's Excel 2003 Formulas.
