Hi All!
I am trying to get column J to calculate total accrued interest for the amount of periods in cell E3. My problem is that the current sumproduct formulas dont account for the declining balances in column G. I couldnt get cumipmt to work either because I couldnt factor in the amortizion. These balances decline based upon the prepayment speed assumption in cell E4. Ultimately I'm trying to be able to enter a # of monthly periods in cell E3 and get a returned total value of accrued interest in cell J19.
Any time spent is greatly appreciated!!!!
I am trying to get column J to calculate total accrued interest for the amount of periods in cell E3. My problem is that the current sumproduct formulas dont account for the declining balances in column G. I couldnt get cumipmt to work either because I couldnt factor in the amortizion. These balances decline based upon the prepayment speed assumption in cell E4. Ultimately I'm trying to be able to enter a # of monthly periods in cell E3 and get a returned total value of accrued interest in cell J19.
Any time spent is greatly appreciated!!!!
Pool Pricer Master Collateral.xls | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | |||
3 | PERIOD | 1 | |||||||||
4 | PREPAYSPEED | 23.33% | |||||||||
5 | ORIGINALPAR | 309,710,005 | |||||||||
6 | AMORTIZEDPAR | 303,688,984 | |||||||||
7 | |||||||||||
8 | Tranche | Rating | Orig,Subordination | Curr.Subordination | ParAmount($) | WAL | Coupon(%) | BondInterest | |||
9 | |||||||||||
10 | ClassA | AAA | 7.95% | 8.11% | $279,067,039 | 3.00 | 1.36% | $316,276 | |||
11 | ClassM-1 | AA+ | 6.25% | 6.37% | $5,265,526 | 5.00 | 1.59% | $6,977 | |||
12 | ClassM-2 | AA | 4.75% | 4.84% | $4,645,764 | 5.00 | 1.69% | $6,543 | |||
13 | ClassM-3 | AA- | 3.75% | 3.82% | $3,096,850 | 5.00 | 1.79% | $4,619 | |||
14 | ClassM-4 | A+ | 3.00% | 3.06% | $2,322,392 | 5.00 | 2.09% | $4,045 | |||
15 | ClassM-5 | A | 2.50% | 2.55% | $1,548,914 | 5.00 | 2.44% | $3,149 | |||
16 | ClassM-6 | A- | 1.50% | 1.53% | $3,096,850 | 5.00 | 2.69% | $6,942 | |||
17 | ClassM-7 | BBB+ | 1.00% | 1.02% | $1,548,914 | 5.00 | 2.99% | $3,859 | |||
18 | ClassM-8 | BBB | 0.50% | 0.51% | $1,547,935 | 5.00 | 3.19% | $4,115 | |||
19 | SoldBondsTotal | $302,140,184 | $356,526 | ||||||||
Execution |