Excel Spreadsheet Assignment
Each student will create a loan amortization table and using Excel based on the following information:
|
|
Loan 1
|
Loan 2
|
Loan 3
|
Loan 4
|
Loan 5
|
|
Mortgage
|
45,000
|
180,000
|
270,000
|
450,000
|
540,000
|
|
interest (6%)
|
0.005
|
0.005
|
0.005
|
0.005
|
0.005
|
|
total payments (30 years)
|
360
|
360
|
360
|
360
|
360
|
|
monthly payment
|
($269.80)
|
($1,079.19)
|
($1,618.79)
|
($2,697.98)
|
($3,237.57)
|
Amortization Schedule for a Loan Payable
Most loans are not repaid in one large payment but paid with smaller installment payments during the agreed upon term of the loan. The payments may be on a monthly, quarterly or semiannual basis but in all cases the payment represents interest and a principal. The payments continue until the loan is completely repaid.
Generally we compute the monthly payment for a mortgage using the following formula:
A = {I x P x (1 + i)n} / {(1 + i)n - 1}
A - the payment amount
i - is the interest rate
P - is the principal or loan amount
n - is the number of periods
The payment is set to make the present value of the payments equal to the amount of the loan at the end of the period. By convention, the quoted mortgage rate is annualized with monthly compounding.
Notes:
- Since we are looking for monthly payment the interest rate needs to be stated monthly (6%/12 = .5%)
- The monthly payment includes interest and principal.
The computation is also available in Excel using the following formula:
@PMT(rate, nper, pv)
Rate - Interest rate (making sure it is stated for the payment ie; monthly, annually or semi-annually)
Nper - number of periods
Pv - present value or the current loan value
Once we know the payment for the life of the loan, we can now create our amortization schedule which will break down the payment between the interest and principal.
|
Amortization schedule for:
|
|
|
|
|
monthly
|
|
|
|
|
Month
|
payment
|
principal
|
interest
|
loan balance
|
|
|
|
6%
|
45,000
|
|
1
|
269.80
|
44.80
|
225.00
|
44,955.20
|
|
2
|
269.80
|
45.02
|
224.78
|
44,910.18
|
|
3
|
269.80
|
45.25
|
224.55
|
44,864.93
|
|
4
|
269.80
|
45.48
|
224.32
|
44,819.45
|
|
5
|
269.80
|
45.70
|
224.10
|
44,773.75
|
|
6
|
269.80
|
45.93
|
223.87
|
44,727.82
|
|
7
|
269.80
|
46.16
|
223.64
|
44,681.66
|
|
8
|
269.80
|
46.39
|
223.41
|
44,635.26
|
|
9
|
269.80
|
46.62
|
223.18
|
44,588.64
|
|
--
|
|
|
|
|
|
--
|
|
|
|
|
|
356
|
269.80
|
263.17
|
6.63
|
1,063.61
|
|
357
|
269.80
|
264.48
|
5.32
|
799.13
|
|
358
|
269.80
|
265.80
|
4.00
|
533.33
|
|
359
|
269.80
|
267.13
|
2.67
|
266.20
|
|
360
|
267.53
|
266.20
|
1.33
|
0.00
|
|
Totals
|
$97,125.73
|
$45,000.00
|
$52,125.73
|
|
Based on the loan amortization schedule we will be able to prepare journal entries for each monthly payment made by the company.
Payment 1
Loan Payable $44.80
Interest Expense $225.00
Cash $269.80
-to record monthly payment on loan
Payment 359
Loan Payable $267.13
Interest Expense $2.67
Cash $269.80
-to record monthly payment on loan