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.

http://www.dailymathproblem.com/2018/11/loans-analyzed-with-pmt-and-pv.html