Bond modeling and related amortization schedules are a little different than your normal schedules. The main feature of this financial model template is to show the user how much interest income they need to recognize in each accounting period based on the idea that the facility (bond / loan) is purchased at either a discount or premium.
After purchase, the template is immediately available to download. It is also included in the accounting templates bundle, the lending businesses bundle, and the Super Smart Bundle.
If you have a bond with a face value of $100,000 and you pay $95,000 for it as the purchaser, that $5,000 difference needs to be recognized as interest income over the term of the loan according to accounting standards and specifically IFRS 9. The coupon, if there is one, should also be counted as income when it is received.
Likewise, if you buy it at a premium (say for $102,000) then that $2,000 reduces the interest income over the same period. The model will do all these calculations automatically as you adjust the various terms.
Template Features:
- Produce dynamic amortization schedules, using monthly, quarterly, biannual, or annual periods.
- Fully integrated 3-statement model from the bond purchaser and issuer point of view (this is where you will see all the cash flows, recognition of interest income (or expense), and all related cash flows and effects on the income statement, balance sheet, and cash flow statement.
- Includes two sensitivity tables to show varying outputs with key variable changes.
- Option for zero coupon bonds and in that scenario, there is an option to manually define the purchase price or auto-calculate the purchase price based on a defined interest rate.
- Includes two sets of visuals (one for scenarios with many periods and one for scenarios with less).
- Instrument name (optional, for reference)
- Face Value (Par Amount)
- Bullet or Amortization ('bullet' means interest-only and a repayment of principal at the end of the term, while amortization is referring to a traditional loan such as a mortgage or car note).
- Annual Coupon Rate
- Nominal or Effective
- Periodic Rate (depending on frequency) - Calculated field.
- Payment Frequency
- Maturity (in years)
- Total Periods - Calculated field.
- Purchase/Acquisition Date
- Define [Rate] or Define [Purchase Price]
- Rate - Use if rate drives purchase price.
- Periodic Rate (depending on frequency) - Calculated field.
- Purchase Price (based on defined rate) - Calculated field.
- Purchase Price (initial fair value) - Use if purchase price is a manual input.
- Purchase Price Used - Calculated field.
- Transaction Costs
- Adjusted Purchase Price - Calculated field.
- Effective Interest Rate (EIR) Calculated
- Periodic Rate (depending on frequency)
- Total Invested
- Total Returned
- Total Profit
- Total ROI
- Average Annualized ROI
- Equity Multiple
- Average Periodic Cash-on-Cash Return
- Average Annualized Cash-on-Cash
- Visualizations
- Dynamic Amortization Schedule
- Two sensitivity tables 1) Sensitizes coupon and purchase price to show varying effective interest rates for the purchaser and 2) Sensitizes effective interest rate and loan term to show varying bond purchase prices (for zero coupon analysis).
