Build a note schedule with AI
Watch each step, check the first row, then open the workbooks.
The note has a face amount of $30,000, a stated rate of 7%, and an effective rate of 9%. It pays interest at each year-end for 5 years and repays principal at the end of year 5. The schedule is from the borrower's side. Slides 44 to 49 explain each step.
- Build the discount-amortization schedule
- Check the completed schedule
- Compare a note issued at matching rates
- Open the workbooks
Build the discount-amortization schedule
Read the prompt
Build a Google Sheets schedule from the borrower’s perspective for this note: • Face amount: $30,000.00 • Stated annual interest rate: 7% • Effective annual interest rate: 9% • Term: 5 years • Interest paid at each year-end • All principal repaid at the end of year 5 • No fees Create a clearly labeled input section. Calculate annual cash interest as face amount × stated rate. Calculate the initial cash proceeds as the present value of the interest payments and maturity principal, using the effective rate. Use cell formulas and references to the inputs, not hardcoded results. Build a five-year table with these columns: 1. Year 2. Opening carrying amount 3. Interest expense = opening carrying amount × effective rate 4. Cash interest paid 5. Discount amortization = interest expense − cash interest 6. Ending carrying amount = opening carrying amount + discount amortization 7. Remaining discount = face amount − ending carrying amount The final row should show carrying amount immediately BEFORE principal repayment. Show the principal repayment separately, reducing the liability to zero. Keep full precision in calculations and display money to two decimals. Add checks that: • Total discount amortization equals the initial discount. • Carrying amount reaches face amount before principal repayment. • Remaining discount reaches zero. Add a line chart showing carrying amount over the five years, starting with the initial proceeds. Include face amount as a reference line. Explain the first row in plain language. Explain why carrying amount increases even though the borrower pays interest each year.
Check the completed schedule
Compare a note issued at matching rates
Read the prompt
Change the effective annual rate from 9% to 7%, keeping the stated rate and all promised payments unchanged. Let the formulas recalculate proceeds and the schedule. Explain why the discount disappears and interest expense now equals cash interest.
Open the workbooks
Both files contain the formulas Gemini wrote. Check the first row before you trust the rest: interest expense is the opening carrying amount times the effective rate, and cash interest is the face amount times the stated rate.
Gemini's commentary uses some terms the course does not. "Coupon rate" means the stated rate, "yield" means the effective rate, and "par value" means the face amount.
Lecture and AI segment
-
1 · Thu 10/15 · Lecture Meeting 14: Measure notes and effective interest
58 slides and the narrated lecture, 35 minutes.
-
2 · AI segment · Demonstration Build a note schedule with AI
Watch Gemini in Google Sheets build the schedule, then download both workbooks.