## 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