J
Jacob
....to do this calculation.
Here is what I'm trying to do:
Devise a calculator that calculates Yield Spread Premium on mortgages.
This is what I do manually, currently:
You take the par rate provided (example: 6%)
You take the final rate provided (example: 7%)
Depending on which lender you use, they calculate the difference between the
two differently.
For instance, I choose Investor A. Investor A states that their buy-up is
..40% to 1. (Which means that taking the final rate, every .4 percent ABOVE
the par rate equals 1% of YSP).
So I wrote into the calculator: (drop down to select investor)
Citifinancial. 7%-6% = 1% divided by .4% = 2.5% YSP
My linked calculations actually look like:
=IF(R37>0,(OFFSET(V28,MATCH(R31,U29:U37,0),0)))
=(R35-R33)/0.4
That isn't so difficult. The problem I'm running into is some investors have
different "buy-ups" for each YSP point. For instance:
Investor B states that the first two YSP points = .5% in rate, and the third
YSP point =.75%.
So using the above example, my YSP would be 2%. If I wanted to increase the
YSP to 3%, I'd need a final rate of 7.75%.
My problem is finding a way to put these differences into a formula
(Calculate the first 2 points differently than the third...)
Perhaps I can explain it easier if I sent someone an example of the actual
spreadsheet....
Jacob
Here is what I'm trying to do:
Devise a calculator that calculates Yield Spread Premium on mortgages.
This is what I do manually, currently:
You take the par rate provided (example: 6%)
You take the final rate provided (example: 7%)
Depending on which lender you use, they calculate the difference between the
two differently.
For instance, I choose Investor A. Investor A states that their buy-up is
..40% to 1. (Which means that taking the final rate, every .4 percent ABOVE
the par rate equals 1% of YSP).
So I wrote into the calculator: (drop down to select investor)
Citifinancial. 7%-6% = 1% divided by .4% = 2.5% YSP
My linked calculations actually look like:
=IF(R37>0,(OFFSET(V28,MATCH(R31,U29:U37,0),0)))
=(R35-R33)/0.4
That isn't so difficult. The problem I'm running into is some investors have
different "buy-ups" for each YSP point. For instance:
Investor B states that the first two YSP points = .5% in rate, and the third
YSP point =.75%.
So using the above example, my YSP would be 2%. If I wanted to increase the
YSP to 3%, I'd need a final rate of 7.75%.
My problem is finding a way to put these differences into a formula
(Calculate the first 2 points differently than the third...)
Perhaps I can explain it easier if I sent someone an example of the actual
spreadsheet....
Jacob