Excel’s Yield Calculation spreadsheets are useful for a variety of ROI analyses. However, simply calculating a single return number often doesn’t tell the whole story about an investment’s potential return. Whether you’re investing in a bond, an annuity, a real estate project, an insurance policy, common stock, or a new business venture, your performance analysis needs to be done differently. Here are some ideas and examples to understand more.
The fundamental analysis of any investment involves the calculation and discount of cash flows. This is usually done in some sort of performance calculation excel spreadsheet that is pre-built for the purpose. A performance template is not a simple calculator. Rather, it requires some thought before you design and build it because investment returns mean different things in different circumstances. For example:
Yield to Maturity (YTM): This is the internal rate of return (IRR) on the total cash flows of the investment, including the purchase price, interest payments, return on principal, and any write-downs or increases. This is the same as the Redemption Yield and assumes the bond will be held to maturity, where there is one. It’s a single calculation in your Excel Yield Calculation spreadsheet using Excel’s IRR function. YTM assumes zero taxes and transaction costs, which is unreasonable in real life, but allows you to compare different investments of different maturities. Yield to maturity assumes there is a defined maturity date, which is not the case for stock investments, venture capital, project finance, or many annuities.
Yield to Call – This is the yield to maturity with the assumption that the yield is calculated using the date of the first call as the maturity. In other words, it assumes that the investor calls the investment at the first available opportunity. This is a challenge to model in an Excel yield calculation spreadsheet if the call function is of the American type, since American options can be bought at any time. Therefore, the investment must have a European or Bermudian calling schedule. If the call schedule is not structured in this way, as many callable bond, private equity, and project finance call features are, then call performance should be based on a simulation of the probability of the call based on different inputs, such as the underlying stock market. prices, interest rates, regulatory approvals, etc.
Dividend Yield – When investing in dividend-paying stocks, the amount of expected dividends is treated as bond coupons, although less accurately in dividend schedules, as dividend amounts can fluctuate or be canceled out entirely. If you want to model dividend yields in an Excel yield calculation spreadsheet, you will likely need to create a simulation model for expected dividends under different market and economic assumptions.
As you can see, there are many practical considerations when creating an Excel spreadsheet for performance calculation.
