Excel assignment 2

BUS 363, Assignment 1, Due Date: on Syllabus

You may submit this assignment using the iLearn assignment link.

Part 1: The formula to calculate the monthly payment, given loan L, term T and annual interest rate R is:

TR

R L

mentMonthlypay −+−



= 12)

12 1(1

12

Excel also provides a PMT function to calculate the monthly payment for a loan. Its syntax is: PMT(rate,

nper, pv) where

Rate: The interest rate for the loan. For calculating the monthly payment, the annual interest rate is

converted to monthly rate (dividing by 12).

Nper: The total number of payments for the loan (Term *12).

Pv: The principal.

Prepare a monthly payment table for loans as shown below, first using the formula and then use the PMT

function. Your spreadsheet should allow user to change the term and the interest rate.

Part 2:

Use Excel to prepare an amortization table (see the example below) for a loan. There are three inputs to this

program: Loan amount, Annual Interest Rate, and Terms. Use the payment function PMT to compute the monthly

payment. The amount to interest column is calculated by multiplying the previous month’s remaining balance by

the monthly rate (annual rate/12). Amount to principal is Payment – Amount to Interest. Remaining Balance is

previous month’s remaining balance – amount to principal. The table should be properly formatted. Test your

worksheet with two sets of data:

1. Loan = 5000, rate = 12%, term = 1 year.

2. Loan = 10000, rate = 9%, term = 1.5 years (18 payments)