If this post looks familiar, it is supposed . My last post was “Market Price VBA Function For MBS (or single loans), Given Yield“. This Excel spreadsheet is almost the same function, except that this function solves for yield given price rather than price given yield. The main difference in the two functions is that yield can’t be solved directly through a formula, it needs an iteration. Iteration means obtaining successively closer approximations to the solution, or in this case the approximations are yields.

My VBA leaves much to criticize, particularly by the purists. I didn’t declare my variables, and I did something you don’t see very often these days in VBA code. I wanted to maintain the same code I used in the price give yield function, without changing any of it (because I am basically lazy), by wrapping the old code inside the iteration. I ran into a problem however, with the balloon portion. I needed a way to complete the iteration from the balloon portion, that exited the function once the balloon payment was reached. The answer I used was to number the line of code that I wanted to jump to and used a “goto” statement.

I was self taught a computer language in the late 60’s call “Basic”. The modern VBA was originally built upon Basic. Code lines were numbered in Basic and that was the way you jumped around within the code. Numbering lines of code is no longer needed in todays VBA, but it still exists. I doubt they even teach numbering lines of code in VBA classes any longer.

Below are the input cells in yellow. Everything looks the same as the price given yield function, except now you enter the market price and the function returns the yield. As I did in the previous spreadsheet, I included an amortization as a proof to check the yield, but it is not necessary to run the function.

God Bless

Download “MortgageYield.xlsm”

Hi Don – I look at a lot of re performing mortgage pools mostly modified performing / sub performing loans. Do you have a quick calculator that would give me a rough market value: for instant $100mm upb, $10mm deferred balance, 4% Gwac, wa current ltv of 90% and I plug in 7-8 cpr, 10% cumulative loss, 20% severity and wala of 6.

Sarperb,

Not specifically. have already viewed “Mortgage Loan Pool – Default Recovery”. You might try and make some adjustments to that pricing model.

Hi I was wondering why you were using the market yield calculated through the function to calculate the PVs of the cashflows in the proof sheet

Hi Vino,

The purpose of the function is to calculate the yield without going through the trouble of creating an amortization schedule. That way the yields can be calculated on a whole portfolio of mortgages. Market price is obtained from market sources. Then, on the Proof sheet, I built an amortization schedule to show that the yield on the amortization schedule matched the yield on the VBA formula.The market price in the function is a given. The amortization method needs to calculates the price first, using PV formulas, given the yield from the function.If the IRR calculated yield, matches the yield on the formula, the formula function yield is correct. It proves that the cash flows of both the function and the amortization are the same, otherwise there would be a problem with the function. You could argue that the fact that the amortization price along is proof, but calculation the yield as a last step just adds to the proof.

Don

How would one adapt this in order to allow for a negative yield?

Hi Paul,

Change code to y1 = -0.25

Should look like:

t = 0.00000001: j = 0: y = 0.7 / 12: y1 = -0.25: y2 = 0

Or, download the workbook again. I change it.

[…] Market Yield VBA Function For MBS (or single loans), Given Price May 12, 2016 […]

[…] Market Yield VBA Function For MBS (or single loans), Given Price May 12, 2016 […]