The blue cells (B6, B7, and B8) should be the only place where you enter numbers without a formula. For example, just enter 100000 for the principal or 5 for one of the rates. B6 is for the principal amount, B7 is for the stated interest rate, and B8 is for the market interest rate. Note that B9 is fixed at 4 years.

Each yellow cell throughout the spreadsheet requires a formula that references other cells. Unlike the blue cells, do not enter any numbers directly into the yellow cells (e.g., don't enter just 100,000 here). These formulas can be as simple as referencing single cells (e.g., =B6) or mathematical operations between cells (e.g., =B6*B7). Some cells will require more complicated formulas such as "if" statements (e.g., =IF(C3>C4,"Higher","Lower")) or complex equations requiring multiple parentheses (e.g., =((1-C4)^C5)/C3).

Note that there could be some slight rounding error in the notes receivable amortization table.

For Cell H6, you need to calculate the initial note present value. Recall that there are two components to calculate the note present value. In class, we used a table of values to find our present value factors; but here we will use actual mathematical formulas.

The mathematical formula for calculating the present value of 1 factor (where n = the number of periods or years and i = the interest rate) is

The mathematical formula for calculating the present value of an ordinary annuity factor (where n = the number of periods or years and i = the interest rate) is

For Cell G4, the word entered here should be able to change from "Premium" to "Discount" and vice-versa depending on whether the note is a premium or a discount. Use "parentheses" around words in your formulas.

For Cells G7 through G10 and also H7 through H10, make sure the calculation changes depending on whether the note is a premium or a discount.

For Cells D15, E20, E24, E28, and E32 make sure that if the note is a "discount" that the amount equals 0. Similarly, for Cells E16, D19, D23, D27, and D31 make sure that if the note is a "premium" that the amount equals 0.

Name: Notes Receivable Amortization Table W N Note Cash Interest Carrying Date Received Revenue Amortized Value Principal Amount 1/1/20X1 $ Stated Rate 12/31/20X1 8 Market Rate 12/31/20X2 Number of Years 4 12/31/20X3 10 12/31/20X4 11 Totals S $ 12 13 Journal Entries Debits Credits 14 1/1/20X1 Notes receivable 15 Premium on notes receivable 16 Discount on notes receivable 17 Cash 18 12/31/20X1 Cash 19 Discount on notes receivable 20 Premium on notes receivable 21 Interest revenue 22 12/31/20X2 Cash 23 Discount on notes receivable 24 Premium on notes receivable 25 Interest revenue 26 12/31/20X3 Cash 27 Discount on notes receivable 28 Premium on notes receivable 29 Interest revenue 30 12/31/20X4 Cash 31 Discount on notes receivable 32 Premium on notes receivable 33 Interest revenue 34 12/31/20X4 Cash 35 Notes receivable 36