Excel Problem Set 1, Revenue Recognition and Matching Concept
A
B
C
D
E
F
G
Length of note
3 years
Amount
$1,000,000.00
Period
Monthly
Annual interest rate
8.00%
Number of periods
36
Monthly interest rate
0.643%
Monthly payment
($31,207.74)
Monthly
Beginning of
Interest
Principal
Month
Payment
month Principal
Expense
Repayment
0
$1,000,000.00
1
$31,207.74
$975,226.29
$6,434.03
$24,773.71
2
$31,207.74
$950,293.18
$6,274.64
$24,933.11
3
$31,207.74
$925,199.66
$6,114.21
$25,093.53
4
$31,207.74
$899,944.68
$5,952.76
$25,254.98
5
$31,207.74
$874,527.21
$5,790.27
$25,417.47
6
$31,207.74
$848,946.20
$5,626.73
$25,581.01
7
$31,207.74
$823,200.60
$5,462.15
$25,745.60
8
$31,207.74
$797,289.36
$5,296.50
$25,911.24
9
$31,207.74
$771,211.40
$5,129.78
$26,077.96
10
$31,207.74
$744,965.66
$4,962.00
$26,245.74
11
$31,207.74
$718,551.05
$4,793.13
$26,414.61
12
$31,207.74
$691,966.49
$4,623.18
$26,584.56
13
$31,207.74
$665,210.88
$4,452.13
$26,755.61
14
$31,207.74
$638,283.12
$4,279.99
$26,927.75
15
$31,207.74
$611,182.11
$4,106.73
$27,101.01
16
$31,207.74
$583,906.74
$3,932.36
$27,275.38
17
$31,207.74
$556,455.87
$3,756.87
$27,450.87
18
$31,207.74
$528,828.38
$3,580.25
$27,627.49
19
$31,207.74
$501,023.14
$3,402.50
$27,805.24
20
$31,207.74
$473,038.99
$3,223.60
$27,984.14
21
$31,207.74
$444,874.80
$3,043.55
$28,164.19
22
$31,207.74
$416,529.40
$2,862.34
$28,345.40
23
$31,207.74
$388,001.62
$2,679.96
$28,527.78
24
$31,207.74
$359,290.29
$2,496.41
$28,711.33
25
$31,207.74
$330,394.23
$2,311.68
$28,896.06
26
$31,207.74
$301,312.26
$2,125.77
$29,081.97
27
$31,207.74
$272,043.17
$1,938.65
$29,269.09
28
$31,207.74
$242,585.76
$1,750.33
$29,457.41
29
$31,207.74
$212,938.82
$1,560.80
$29,646.94
30
$31,207.74
$183,101.14
$1,370.05
$29,837.69
31
$31,207.74
$153,071.48
$1,178.08
$30,029.66
32
$31,207.74
$122,848.60
$984.87
$30,222.87
33
$31,207.74
$92,431.27
$790.41
$30,417.33
34
$31,207.74
$61,818.23
$594.71
$30,613.04
35
$31,207.74
$31,008.23
$397.74
$30,810.00
36
$31,207.74
($0.00)
$199.51
$31,008.23
Effective interest method
The table below calculates monthly interest payments and separates those payments into their interest and principal
repayment components.
This example shows a 3-year, $1 million, 8% note to be repaid in 36 equal monthly payments.
The 8% annual interest rate equals a .643% monthly interest rate, compounded monthly:
(1 + .08) ^ (1/12) -1.
The monthly payment is computed using Excel's PMT function [ =pmt(interest rate, number of periods, amount) , or
pmt(.00643, 36, 1000000) ], which equals $31,207.74.
The table separating the payment between interest and principal
is derived as follows:
Column B shows the monthly payment from the previous paragraph.
Column C begins with the initial loan amount,
$1,000,000.
Column D shows the interest expense on that $1,000,000 at a monthly rate of .643%, or $6,434.03 for the
first month.
Since the payment is $31,207.74 and the interest is only $6,434.03, the difference is principal repayment,
or $24,773.71.
Since we have repaid $24,773.71 of principal, the new principal in column C becomes $975,226.29 ($1,000,000 -