SmartHelping / Bonds & Loans / Excel
Effective Interest Rate Template
Calculate interest income and expense for bonds or loans acquired at a discount or premium. Connect purchase price, coupon terms and repayment structure to amortization schedules, financial statements and investment returns.
Immediate download after purchase. By purchasing, you agree to the Terms of Service.
See the template in action
Walk through the terms, schedules and accounting views.
Follow the video to see how the instrument inputs feed effective interest calculations, amortization, financial statements and return analysis.
Template features
Connect the instrument terms to the full financial result.
Review interest recognition, cash flows and returns from both sides of the instrument, with flexible payment periods and pricing assumptions.
Choose the schedule that fits the instrument
Build amortization schedules with monthly, quarterly, semiannual or annual periods. Model bullet repayment at maturity or an amortizing payment structure.
Follow the effects through three financial statements
Review fully integrated income statements, balance sheets and cash flow statements from both the bond purchaser and issuer perspectives, including interest income or expense and related cash flows.
Enter a price or calculate it from a rate
Model zero-coupon bonds with a manually defined purchase price or a purchase price calculated from the interest rate you specify.
See the rate and the full investment result
Calculate the effective interest rate and its periodic equivalent. Review total invested, total returned and total profit alongside the amortization schedule.
Compare ROI, equity multiple and cash returns
Review total ROI, average annualized ROI, equity multiple, average periodic cash-on-cash return and average annualized cash-on-cash return.
Compare rate and purchase-price scenarios
Vary coupon rate and purchase price to see the purchaser's effective interest rate. For zero-coupon analysis, vary effective interest rate and loan term to compare bond purchase prices.
Review schedules with more or fewer periods
Use the two sets of visualizations provided for scenarios with many periods and scenarios with fewer periods.
Work directly with the yellow input cells
The Excel template is fully editable and unlocked. Change the yellow input cells and use the walkthrough to follow the structure and calculations.
Instrument inputs
Define the payment terms and acquisition economics.
Enter the instrument details and pricing assumptions, then review the rate, period and purchase-price fields calculated by the model.
Set the face value and acquisition date
Enter an optional instrument name for reference, the face value or par amount, and the purchase or acquisition date.
Choose bullet repayment or amortization
Select a bullet structure with principal repaid at the end of the term, or an amortizing structure such as a traditional mortgage or car loan. Coupon payments follow the terms you enter.
Define the annual coupon assumptions
Enter the annual coupon rate and choose nominal or effective treatment. The model calculates the periodic rate for the selected payment frequency.
Set the frequency and maturity
Choose monthly, quarterly, semiannual or annual periods and enter maturity in years. Total periods are calculated from the timing assumptions.
Choose which assumption drives the calculation
Define a rate or enter the purchase price as an initial fair value. The model provides the corresponding periodic rate, calculated purchase price where applicable, and the purchase price used in the analysis.
Include the costs of the acquisition
Enter transaction costs and review the adjusted purchase price calculated by the template alongside the instrument terms and investment outputs.
Discounts, premiums and zero-coupon instruments
See how purchase price affects interest recognition.
Use the effective interest method to follow the discount or premium through the accounting periods, alongside the instrument's cash flows.
Purchased at a discount
Before transaction costs, a bond with a $100,000 face value purchased for $95,000 has a $5,000 discount. The model schedules recognition of that discount over the instrument's term using the effective interest method, alongside any coupon cash flows.
Purchased at a premium
Before transaction costs, paying $102,000 for the same $100,000 face value creates a $2,000 premium. Amortizing that premium reduces the purchaser's interest income over the term. The schedule updates as the purchase price and other terms change.
Zero-coupon analysis
Model an instrument with no periodic coupon payments. Enter the purchase price directly or calculate it from a defined rate, then review effective interest, the amortization schedule, financial statements and investment returns.
How to use it
From instrument assumptions to interest and return analysis.
Define the instrument and payment terms
Enter face value, acquisition date, maturity, coupon rate, rate basis and payment frequency. Choose bullet repayment or an amortizing structure.
Set the rate or acquisition price
Select whether to define a rate or purchase price, add transaction costs and review the price and periodic-rate fields calculated by the model.
Review the schedule and financial statements
Follow the dynamic amortization schedule and effective interest calculations, then review the income statement, balance sheet and cash flow statement for the purchaser and issuer.
Compare returns and sensitivities
Review invested and returned cash, profit, ROI, equity multiple and cash-on-cash metrics. Use both sensitivity tables and the appropriate set of visualizations for the number of periods modeled.
Who gets value from it
Built for the people accounting for and evaluating the instrument.
Accounting teams
Review periodic interest recognition and the financial statement effects of a discounted or premium-priced instrument.
Bond and loan purchasers
Evaluate purchase price, coupon terms, effective interest rate and investment return metrics.
Issuers and finance teams
Follow the instrument from the issuer perspective, including interest expense, balances and cash flows.
Financial modelers
Work with dynamic payment schedules, zero-coupon pricing and sensitivity analysis in an editable Excel workbook.
Also available in these bundles
Need more accounting and financing tools?
This effective interest rate template is included in the following SmartHelping collections.
Accounting Templates Bundle
Explore spreadsheets for accounting calculations, financial analysis and tracking.
View Accounting BundleLending & Credit Models Bundle
Explore models for lending businesses, loan portfolios, financing and credit analysis.
View Lending & Credit BundleSuper Smart Bundle
Get the complete SmartHelping template collection for operating forecasts, valuation, finance and more.
View Super Smart BundleRelated templates
Explore more loan, financing and amortization models.
Loan Securitization Facilitator
Plan a securitization platform or facilitator with fee revenue, deal costs and capital requirements.
Explore this templateLoan Securitization
Analyze the structure and economics of an individual loan securitization deal.
Explore this templateInterest Rate Swap
Evaluate the cash flow implications of an interest rate swap.
Explore this templateMortgage Savings Calculator
Compare mortgage repayment scenarios and potential savings.
Explore this templateSeller Financing Calculator
Model seller-financed payments and amortization, including tax basis calculations.
Explore this templateDynamic Amortization Schedule for Startup Modeling
Build dynamic loan amortization schedules for startup financial modeling.
Explore this templateBuy Now, Pay Later Firm Startup Model
Plan the financial performance of a buy now, pay later business.
Explore this templateDirect Lending Business Startup Model
Build an operating forecast for a direct lending business.
Explore this templateQuestions before you choose
A few useful details.
Can I model both discounts and premiums?
Yes. Enter the acquisition price and instrument terms to review effective interest and the amortization of a discount or premium over the schedule.
Which payment frequencies are supported?
The model supports monthly, quarterly, semiannual and annual periods. Maturity and payment frequency determine the total number of periods.
Can I analyze zero-coupon bonds?
Yes. Enter a purchase price manually or have the model calculate it from a defined rate. A dedicated sensitivity table compares zero-coupon purchase prices as the effective interest rate and loan term change.
Does the model include both purchaser and issuer statements?
Yes. Both perspectives include an integrated income statement, balance sheet and cash flow statement so you can follow interest recognition, balances and cash movements.
What do the two sensitivity tables show?
The first varies coupon rate and purchase price to show the purchaser's effective interest rate. The second varies effective interest rate and loan term to show bond purchase prices for zero-coupon analysis.
Can I reduce the workbook's calculation time?
Yes. If you do not need the sensitivity analysis, you can clear out the two sensitivity tables to reduce the processing they require.
Is the template editable and included in a bundle?
Yes. The Excel workbook is fully unlocked and editable, with yellow input cells. It is included in the Accounting, Lending & Credit and Super Smart bundles linked above.
Bring effective interest, cash flow and accounting together
Understand the financial result behind the bond terms.
Purchase the effective interest rate template for $45 and receive immediate access to the download.
