Develop a macro that will calculate the monthly payment for a loan given the following:
The loan amount i.e. principal (dollars)
The annual interest rate (%/yr)
The number of years to pay off the loan (yrs)
Use the InputBox command to give default input values based on the last case you ran.
Base the monthly payment according to the formula:
a = p(i)(1+i) n / ( (1+i) n – 1 )
Generate a table with headings showing the month number, the monthly payment, the
monthly interest, the amount applied to the principal, and the new balance.
Generate the tables for the following cases:
- p=$25,000, I=5%, N=5 yrs
- p=$25,000, I=6%, N=6 yrs
- p=$400,000, I=5%, N=30 yrs
- p=$400,000, I=5%, N=10 yrs
- p=$400,000, I=6%, N=30 yrs
- p=$400,000, I=5%, N=30 yrs, round monthly payments and interest
to the nearest penny
In your report, show an abbreviated payment schedule of the first six and the last 6
payments for each case. Discuss your findings.
Sample Solution
