{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Excel Problem 1

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

This preview shows pages 1–3. Sign up to view the full content.

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 - \$24,773.71). If we continue that process for each of the 36 months, interest expense declines each month as the principal declines; by the end of the 36 month loan period the principal is reduced to zero.

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

View Full Document
Excel Problem Set 1, Revenue Recognition and Matching Concept Notes: 1. When you compute the note payment schedule, each note is for \$90,000, not \$100,000, since there is a \$10,000 down payment. 2. If Patton were to recognize the entire sale as revenue at contract signing, it would make the following JEs: Original land purchase Land inventory \$2,000,000 Cash \$2,000,000 Initial Sale Cash \$400,000 Note Receivable \$3,600,000 Land Revenue \$4,000,000 Cost of land sales \$2,000,000 Land inventory \$2,000,000 Administrative expenses \$1,000,000 Cash \$1,000,000 Note that Patton receives cash of \$400,000 and pays cash of \$3,000,000. The only way to operate a business like this is to start with a large amount of cash or else sell stock and bonds to the public. It is likely that the only way to sell stock and bonds to the public is to report high profits. 3. For each of the 36 months, Patton records a debit to cash that is identical each month. However, the entries to Notes Receivable and Interest Revenue (or Interest Income ) change because the principal is being repaid.
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### Page1 / 7

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

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

View Full Document
Ask a homework question - tutors are online