PrefBlog

Break-Even Rate Shock Calculator

Written by

jiHymas

in

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.

Improved BERS Calculator

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.

Comments

7 responses to “Break-Even Rate Shock Calculator”

  1. Norbert Schlenker

    Have you considered using the standard Solver function (under Tools) instead of having the spreadsheet’s user fiddle the BERS manually?

  2. jiHymas

    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!

  3. PrefBlog » Blog Archive » New Issue: EPP FixedReset 7.00%+418

    […] 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 […]

  4. PrefBlog » Blog Archive » Research: Break-Even Rate Shock

    […] The related Break-Even Rate Shock Calculator has been published previously. […]

  5. PrefBlog » Blog Archive » New Issue: BAM FixedReset 5.40%+230

    […] night yielding around 6.80%, therefore the Break-Even Rate Shock for the issue, according to the BERS Calculator is a very high […]

  6. New Issue: SLF FixedReset 4.35%+141 « PrefBlog

    […] 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), […]

  7. New Issue: GWO FixedReset 3.65%+130 « PrefBlog

    […] 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, […]

Leave a Reply

More posts