Question: A person is buying a $300,000 house. She is taking out a 30year 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 30year
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 15year 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