In: Finance
What is the present value of a $15 million pool of 30-year
mortgages with an 10.5 percent per year monthly mortgage coupon if
market rates are 7 percent? The GNMA guarantee fee is 7 basis
points and the FI servicing fee is 43 basis points.
a. Assume that the GNMA pass-through is fully
amortized.
b. Assume that the GNMA pass-through is only half
amortized. Market rates are still 7 percent. If there is a lump sum
payment at the maturity of the GNMA pass-through that equals 60
percent of the mortgage pool's face value, find the present value
of the pass-through.
PLEASE PROVIDE EXCEL FORMULA SPELLED OUT TO ANSWER THESE PROBLEMS. SPECIFICALLY QUESTION B. I'm having trouble understanding how to get the answer properly I understand how to get answer A.
formula for PV of annuity:-
P = $15 million;
n = 12*30 = 360;
i = The GNMA monthly coupon rate = 10.5 - (0.07 + 0.43)
= 10.5 ‑ 0.5 = 10% P.A. = 10/12 = 0.8333% = .0083
R (monthly payments) = ?
a). Assuming GNMA is fully amortized
15,000,000 = R*[{1-(1.00833)-360}/0.0083]
R = 15,000,000 / 113.989
=> R = $131591.4
Calculating PV @ 7% market rate using monthly payments (R) calculated above:-
R = 131591.4; n = 360;
i = 7/12 = 0.5833% PA => 0.0058
P=?
P = 131591.4 * [{1-(1.00583)-360}/0.00583]
=> $19787172.4
b) The GNMA pass-through is only half amortized., assume there is a lump sum payment at the maturity of the GNMA pass-through that equals 60% of the mortgage pool's face value
15,000,000 = R*[{1-(1.00833)-360}/0.0083] + 7,500,000 / (1.00833)360
15,000,000 = R * 113.989 +378524
R = 14621476/113.989
R= $128270.94
R = 128270.94; n = 360;
i = 7/12 = 0.5833% PA => 0.0058
P=?
P = 131591.4 * [{1-(1.00583)-360}/0.00583] + 9,000,000 / (1.00833)360
=> 131591.4 * 150.3683 + 454228.75
=> 19787175.11 + 454228.75
P = $20241403.86