A Recursive Approach For Calculating The Effective Yield On Bonds When Settlement And/Or Expected Maturity Is Other Than An Interest Payment Date.

Abstract

Standard spreadsheet functions cannot compute bond yields if both the settlement and maturity dates fall on other than an interest payment date. Using reciprocal cell references, the RATE function is enhanced to overcome this limitation. An appendix extends the enhancement procedure to the IRR function. The procedure demonstrated has applications to a wide variety of financial transactions which are structured around an initial cash flow followed by an annuity and a lump sum, for example, a single advance loan with an odd first and/or last payment period and a terminal balloon payment.