Excel Problem 1 - Excel Problem Set 1, Revenue Recognition...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
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 -
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 04/07/2008 for the course ACCY 303 taught by Professor Sandretto during the Spring '08 term at University of Illinois at Urbana–Champaign.

Page1 / 7

Excel Problem 1 - Excel Problem Set 1, Revenue Recognition...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online