Subscribers to PrefLetter and Canadian Moneysaver will know what this is!
The rest of you will have to wait until I republish the articles in about a month’s time.
BERS Calculator (MS-Excel Spreadsheet)
Update, 2009-10-1: This spreadsheet was originally prepared by James Hymas of Hymas Investment Management Inc. The macro for automatic calculation of the Break Even Rate Shock was developed by Norbert Schlenker of Libra Investments.
Update, 2010-9-26: Following comments on Financial Webring Forum, I have added a feature to the standard spreadsheet, above, that allows the user to specify that the issue will be called on the next reset date. This may be useful for those seeking to compare a high-premium, almost-certain-to-be-called FixedReset to a PerpetualDiscount.
New version allowing call certainty for high-premium FixedResets.
Update, 2010-9-27: like_to_retire, one of Financial Webring’s more reliable posters, claims:
Note that the calculator macro is incompatible with Excel 2007 unless the user deletes the reference for the add-in component Solver.xla and adds a new reference for Solver.xlam.
I do not have the facilities for checking this out myself, but thought I’d pass it on.
Have you considered using the standard Solver function (under Tools) instead of having the spreadsheet’s user fiddle the BERS manually?
Actually, no I haven’t!
I tend to divide electronic calculations into two groups: programmes and one-offs … this wasn’t big enough to be a programme and one-off’s tend to get whipped off without worrying much about whether the user has to fiddle or not.
However, all contributions will be gratefully accepted and acknowledged!
[…] EPP.PR.A closed today at 16.55b to yield 7.42% at the bid price. Therefore, according to the BERS Calculator (and, of course, the assumptions embedded therein), the Break-Even Rate Shock is […]
[…] The related Break-Even Rate Shock Calculator has been published previously. […]
[…] night yielding around 6.80%, therefore the Break-Even Rate Shock for the issue, according to the BERS Calculator is a very high […]
[…] about 6.50%, meaning that the spread for the FixedReset is -235bp. Plugging these numbers into the Breakeven Rate Shock Calculator (which I have discussed in a free publication and at greater length in a 2009 issue of PrefLetter), […]
[…] we’ll plug the following into the Break Even Rate Shock Calculator: PD Yield 5.40%, FR Spread -1.75%, Term 5 Years and come up with a Break Even Rate Shock of 257bp, […]
[…] related Break-Even Rate Shock Calculator has been published previously, as has the Moneyletter version of this […]