Yield Calculation Excel Spreadsheets - Tips and Tricks

[ad_1]

Yield calculation Excel worksheets are useful for a variety of investment return analyses. However, simply calculating a single yield number does not usually provide the whole story about the potential profitability of an investment. Whether you are investing in a bond, annuity, real estate project, insurance policy, common stock, or new business venture your yield analysis should be done differently. Here are some insights and examples to understand more.

Fundamental analysis of any investment involves the computation and discounting of cash flows. This is typically done in some sort of a yield calculation Excel spreadsheet which is pre-built for the purpose. A yield template is not a simple calculator. Rather, it requires some thought before you design and build it because investment yields mean different things under different circumstances. For example:

Yield to Maturity (YTM): This is the Internal Rate of Return (IRR) of the investment's total cash flows, including purchase price, interest payments, principal return, and any amortization or accretion. 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 computation in your yield calculation Excel worksheet using Excel's IRR function. YTM assumes zero taxes and transaction costs which is unreasonable in real life, yet it does allow for comparing different investments of differing maturities. Yield to Maturity assumes there is a defined maturity date, which is not the case for equity, venture capital, project finance, or many annuity investments.

Yield to Call: This is the yield to maturity with the assumption that the yield is calculated using the first call date as the maturity. In other words, it assumes the investment is called by the investor at the first available opportunity. This is a challenge to model in a yield calculation Excel spreadsheet if the call feature is the American type, since American options can be called at any time. So the investment must have a call schedule that's European or Bermudan. If the call schedule is not structured in this way as many callable bonds, private equity, and project finance call features are, then the Yield to Call must be based on a simulation of the likelihood of call based on different inputs like underlying stock market prices, interest rates, regulatory approvals, etc.

Dividend Yield: When investing in dividend-paying stocks, the amount of expected dividends is considered like bond coupons, albeit with less accuracy in dividend schedules since dividend amounts can vary or be cancelled entirely. If you want to model dividend yields in a yield calculation Excel worksheet then it's likely you will need to build a simulation model for expected dividends under different economic and market assumptions.

As you can see there are many practical considerations when building up a yield calculation Excel spreadsheet.

[ad_2]

Source by Dann Roberts