I now also have another function to calculate yield given price see:
Many of my previous posts and Excel spreadsheets have included amortization schedules that can calculate the price of MBS pools or single loans, given a discount rate (market yield). This spreadsheet includes a UDF (user defined function) that allows the user to calculate the price, without having to construct an amortization schedule. I do include an amortization schedule as a proof however, that the function is working correctly. My posts on the mega-formula for calculating price, given yield gave the same basic results, but it did not have all of the inputs allowed with this function.
Below, you can see all the variables that can be included in the calculation, however only the market yield, remaining months, and gross rate are required inputs. The rest are optional. Optional inputs include, servicing fee, prepayment rate (CPR), balloon months remaining, interest only months remaining, default rate (CDR), loss severity, and payment delay (days). By default, I included a value in each of the input cells, only to show how each of the inputs should be formatted. Most users will not use all the possible inputs for any given pool.
On the input sheet called Function, next to the input cells, are explanations of how some of the inputs should be used. For example, if you have a loan with a 60 month balloon and the loan is interest only, the interest only should be 59 months, not 60 to allow for the last payment which is all principal. The same would hold for any interest only loan. Also, mortgage payments have 30 days of accrued interest at each payment, so the default delay is 30 days. If payments are assumed to be paid at end of a grace period, for example (15th of the month rather than the 1st) the delay would be 44 days. Some agency MBS have longer delays.
The UDF is called “Mortgage_Price”. Below I show that the function arguments are fairly self-explanatory:
The “Proof” sheet has an amortization schedule used to proof the results of the UDF. There are no input cells on this sheet. It also shows a break down of the present value of each of the components of the loan pool, to get to the final price.
On the sheet called “VBA”, I included the function’s VBA. I divided up the calculations to show the steps involved. The logic was the same as I would use to construct an amortization schedule. As the “For I = 1 to rm” line loops through each payment, it accumulates the present value of each cash flow that will be paid (based on $100 in par value) to the holder of the pool. That accumulation is the market price.
I must include a disclaimer, in that I have not compared all combinations of input. I do not have access to Bloomberg or other systems to confirm my results. Therefore use at your own risk. No guarantee is either stated or implied.
Download Excel Spreadsheet: