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”