Thursday, November 8, 2018

Loans Analyzed with PMT and PV




Question One:   A person has $25,000 to lend for 20 years.

What is the monthly payment received if the annual interest rate is 3 percent per year?  

What is the monthly payment received if the annual interest rate is 6 percent per year?

Answer:  This problem is solved below with the PMT function.

Impact of Interest Rates on Bond Payments
rate
nper
PV
PMT
0.03
240
25000
$138.65
0.06
240
25000
$179.11
Diff.
$40.46


Question Two: How much should a person loan to receive a $300 monthly payment on a loan when the annual interest rate is 3 percent per year?

How much should a person invest to receive a $600 monthly interest payment on a bond at the same 3 percent annual interest rate?

Answer:   The required loan is calculated with the PV function.

Required Bond Purchase
Rate
nper
PMT
PV
0.03
240
300
$54,093.27
0.03
240
600
$108,186.55

Note that when the interest rate is doubled and the term (nper) is unchanged the required bond purchase doubles.

Note also that the two problems presented here involve loans not bonds.   The output from the PMT function is both interest and repayment of principal.


More questions solving finance problems in Excel can be found here.






No comments:

Post a Comment