Wednesday, September 6, 2017

Working Backwards With the FV function in Excel



Question:   A person is buying a $300,000 house.   She is taking out a 30-year FRM loan with an interest rate of 4.0 percent.   She wants the loan balance to be $170,000 in ten years.   What down payment should she place on her house to assure that the loan balance is $170,000 in ten years?

Method:  I solve this problem by trial and error

The initial loan balance and the monthly payment amount are inputs to the FV function.   The solution to this problem involves finding the inputs -- initial loan amount and the monthly payment at a 30-year term – that lead to a FV of $170,000.  

MY first guess for the initial loan balance is $250,000.   The PMT amount of $1,194 was obtained from the PMT function.  Plug both into the FV function and I get a future loan balance of $196,000.

This is too high so I lower the loan balance to $210,000 and calculate the corresponding monthly payment.   Both are again plugged into the FV function.   The FV of the loan balance of $165,446 is a bit too low. 

My final guess for the loan balance of $216,000 is close.


Trial by Error Solution for Initial Loan Balance
Rate
0.0033
0.0033
0.0033
NPER
360
360
360
LB
-$250,000
-$210,000
-$216,000
PMT
$1,194
$1,003
$1,031
FV
$196,960
$165,446
$170,173


The down payment is the house value minus the initial loan balance, which is $84,000 ($300,000-$216,000).

I check the answer using the CUMPRINC function.






Check Using CUMPRINC
RATE
0.003333333
NPER
360
PV
$216,000
Start Period
1
End Period
120
Type
0
CUMPRINC
-$45,827
Ending Balance
$170,173
I get the same answer from both methods.

The interested reader should solve the problem for a 15-year FRM at a lower interest rate, say 3.5 APR.

Authors Note:   This is Problem Ten in my tutorial on Excel Finance functions.  

Excel Finance Function Tutorial







No comments:

Post a Comment