Market Yield VBA Function For MBS (or single loans), Given Price

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


Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.


  1. 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.

    1. Sarperb,

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

  2. 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

    1. 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.


    1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *