Question

You are looking to do an addition to your home and anticipate taking a loan to pay for it.

The proposed cost of the improvements is $30,000.00. After speaking with the bank, you

can obtain a 10 year loan at 5% annual interest. Using the interest rate charts is

acceptable, if applicable.

3A)

Prepare an amortization chart, similar to the one discussed in class, for the loan if it is paid

back on a yearly basis. Show all calculations (separate from the table) for the yearly loan

payment, interest paid and principal paid. Show the sums for all interest, principal and

yearly payments made. Adding a column for cumulative monthly principal payments is

helpful.

3B

In a separate calculation, what would the monthly loan payment be if you decided to make

monthly payments instead? What is the total amount paid on the loan? What is the total

principal paid? What is the total interest paid on the loan? Show all calculations. No need

to do an amortization chart for the monthly option.

Answer 1

3a]

Annual payment is calculated using PMT function in Excel :

rate = 5%

nper = 10

pv = -30000

PMT is calculated to be $3,885.14

Interest component of yearly payment = principal outstanding at beginning * interest rate

Principal component of yearly payment = yearly payment - interest component of yearly payment

principal outstanding at end = principal outstanding at beginning - principal component of yearly payment

3 b]

Monthly payment is calculated using PMT function in Excel :

rate = 5% / 12 (converting annual rate into monthly rate)

nper = 120 (10 years with 12 monthly payments each year = 10 * 12 = 120)

pv = -30000

PMT is calculated to be $318.20

Total amount paid = monthly payment * total number of payments = $318.20 * (10 * 12) = $38,184

total principal paid = loan amount = $30,000

total interest paid = Total amount paid - total principal paid =  $38,184 - $30,000 = $8,184