the original balance of the pool is $100,000 The pass-through rate is 9.0% WAM is 360...

   

Transcribed Image Text:

the original balance of the pool is $100,000 The pass-through rate is 9.0% WAM is 360 months, and WAC is 9.5% Assume a prepayment rate of 165PSA Simulate the full schedule in next worksheet MAKE SURE ALL CELLS ARE CODED HOW YOUR WORK WILL BE JUDGED: I WILL CHANGE AN INPUT VARIABLE (MARKED IN RED IN WORKSHEET) ALL YOUR SCHEDULE MUST RECOMPUTE AUTOMATICALLY, CORRECTLY. Home Insert Draw Page Layout fx [. ? V C26 1 2 3 4 5 6 7 8 9 LO 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 AutoSave OFF 28 29 30 31 32 33 34 35 36 37 38 39 10 1 12 ▼ C Insert AutoSum Recently Financial Logical Function Used V ✓ fx D computes =PV computes =RAT computes =IRR computes=NPV computes =pmt computes =npel V computes=fv V E 5. C.. Formulas A Text V 0 -250 0 -250 V 0.11 0 =PV(F1, T2, G3, T4,0) =IRR(F12:T12) Date & Time =RATE(T6,G7,F7,T8,0) 0.11 0 Data 0.11 0 F 0.01 0 -2500 =NPV(F14, G16:T16)+F16 0.11 0 Lookup & Reference -162.8368 =PMT(F19, T20, F21, T22,0) V -162.8368 =NPER(F25, K27,F27, T28,0) -162.8368 =FV(F31, T32,J33, F33,0) Review Ө Math & Trig v 1 20 1 20 1 20 1 20 1 20 1 20 1 20 View ... V More Functions G Automate 2 20 2 20 2 20 2 253 2 20 2 20 2 20 Name Manager H 3 2 20 3 20 3 20 3 214 3 20 3 20 3 20 X 4302 ASSIGN 02 (1) Tell me Define Name ✓ Use in Formula v Create from Selection I 4 20 4 20 4 20 4 523 4 20 4 20 4 20 J 5 20 5 20 5 100 5 200 5 20 5 20 5 20 K Trace Precedents Trace Dependents Remove Arrows ✓ 6 20 6 20 6 100 6 200 6 20 6 20 6 20 L 7 20 7 20 7 100 7 200 7 20 7 20 7 20 7fx lea Show Error Watch Formulas Checking Window M 8 20 8 20 8 100 8 200 8 20 20 8 20 N v 9 20 9 20 9 200 9 200 9 20 9 20 9 20 O Calculation Calculate Sheet Options 10 20 10 20 10 200 10 200 10 20 10 20 10 20 P 11 20 11 20 11 200 11 200 11 20 11 20 Calculate Now 11 20 Q 12 20 12 20 12 20 12 200 12 20 12 20 12 20 R 13 20 13 20 13 20 13 20 13 20 13 20 13 20 Comments S 14 20 100 14 20 100 14 20 14 100 14 20 100 14 20 100 14 20 T Sh C26 1 Home Insert Draw Page Layout fx Insert Function 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 A B [. V C D AutoSum Recently Financial Logical Used fx computes =PV computes =RATE computes =IRR E computes=NPV computes =pmt computes =nper computes=fv V ? V F Formulas A Text 11.0000% 0 ($162.8368) 4.9219% 0 22.0176% 1 ($250.00) 20 0 ($250.00) 1.0000% 0 ($2,500.00) $53.31 11.0000% 0 ($162.8368) 20 11.0000% 0 ($162.8368) 14 11.0000% 0 ($162.8368) G 100 V 1 20 1 20 1 20 1 20 1 20 1 20 Date & Time H 2 20 2 20 2 20 2 20 V 2 20 2 20 Data Lookup & Reference I 3 20 3 20 2 3 253 214 3 20 3 20 3 20 3 20 J 4 20 4 20 4 523 4 20 Review 4 20 4 20 ӨГ K Math & Trig 4 5 20 100 5 20 5 20 5 200 5 20 5 20 V 5 20 L 6 20 More Functions 6 20 6 100 6 200 6 20 6 20 View 6 20 ... M 20 7 20 7 100 7 200 20 7 20 7 20 N Automate 8 20 8 20 8 100 8 200 300 8 20 8 20 8 20 Name Manager O 9 20 9 20 9 200 9 200 9 20 9 20 9 20 P 10 20 10 20 10 200 10 200 10 20 10 20 10 20 Tell me Define Name ✓ Use in Formula ✓ Create from Selection Q 11 20 11 20 11 200 11 200 11 20 11 20 11 20 R 12 20 12 20 12 20 12 200 12 20 12 20 12 20 S 13 20 13 20 13 20 13 20 13 20 T 13 20 14 20 100 13 14 20 100 14 20 100 14 20 14 20 100 14 20 100 14 20 U Tra Tra Re V J8 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 A INPUT Month Number 123 4567 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 B WAC Beginning Balance $100,000 fx C 9.500% CPR% D SMM% E WAM Mortgage PMT Expected F 360 Interest$ paid to Investors G PASS THROUGH RATE Scheduled Principal PMT H 9.000% Estimated Principal Pre-PMT POOL$ Total Principal paid to Investors $ J 100,000 Total Cash Flow to Investors K Pool Fees L PSA M 165 N O the original balance of the pool is $100,000 The pass-through rate is 9.0% WAM is 360 months, and WAC is 9.5% Assume a prepayment rate of 165PSA Simulate the full schedule in next worksheet MAKE SURE ALL CELLS ARE CODED HOW YOUR WORK WILL BE JUDGED: I WILL CHANGE AN INPUT VARIABLE (MARKED IN RED IN WORKSHEET) ALL YOUR SCHEDULE MUST RECOMPUTE AUTOMATICALLY, CORRECTLY. Home Insert Draw Page Layout fx [. ? V C26 1 2 3 4 5 6 7 8 9 LO 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 AutoSave OFF 28 29 30 31 32 33 34 35 36 37 38 39 10 1 12 ▼ C Insert AutoSum Recently Financial Logical Function Used V ✓ fx D computes =PV computes =RAT computes =IRR computes=NPV computes =pmt computes =npel V computes=fv V E 5. C.. Formulas A Text V 0 -250 0 -250 V 0.11 0 =PV(F1, T2, G3, T4,0) =IRR(F12:T12) Date & Time =RATE(T6,G7,F7,T8,0) 0.11 0 Data 0.11 0 F 0.01 0 -2500 =NPV(F14, G16:T16)+F16 0.11 0 Lookup & Reference -162.8368 =PMT(F19, T20, F21, T22,0) V -162.8368 =NPER(F25, K27,F27, T28,0) -162.8368 =FV(F31, T32,J33, F33,0) Review Ө Math & Trig v 1 20 1 20 1 20 1 20 1 20 1 20 1 20 View ... V More Functions G Automate 2 20 2 20 2 20 2 253 2 20 2 20 2 20 Name Manager H 3 2 20 3 20 3 20 3 214 3 20 3 20 3 20 X 4302 ASSIGN 02 (1) Tell me Define Name ✓ Use in Formula v Create from Selection I 4 20 4 20 4 20 4 523 4 20 4 20 4 20 J 5 20 5 20 5 100 5 200 5 20 5 20 5 20 K Trace Precedents Trace Dependents Remove Arrows ✓ 6 20 6 20 6 100 6 200 6 20 6 20 6 20 L 7 20 7 20 7 100 7 200 7 20 7 20 7 20 7fx lea Show Error Watch Formulas Checking Window M 8 20 8 20 8 100 8 200 8 20 20 8 20 N v 9 20 9 20 9 200 9 200 9 20 9 20 9 20 O Calculation Calculate Sheet Options 10 20 10 20 10 200 10 200 10 20 10 20 10 20 P 11 20 11 20 11 200 11 200 11 20 11 20 Calculate Now 11 20 Q 12 20 12 20 12 20 12 200 12 20 12 20 12 20 R 13 20 13 20 13 20 13 20 13 20 13 20 13 20 Comments S 14 20 100 14 20 100 14 20 14 100 14 20 100 14 20 100 14 20 T Sh C26 1 Home Insert Draw Page Layout fx Insert Function 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 A B [. V C D AutoSum Recently Financial Logical Used fx computes =PV computes =RATE computes =IRR E computes=NPV computes =pmt computes =nper computes=fv V ? V F Formulas A Text 11.0000% 0 ($162.8368) 4.9219% 0 22.0176% 1 ($250.00) 20 0 ($250.00) 1.0000% 0 ($2,500.00) $53.31 11.0000% 0 ($162.8368) 20 11.0000% 0 ($162.8368) 14 11.0000% 0 ($162.8368) G 100 V 1 20 1 20 1 20 1 20 1 20 1 20 Date & Time H 2 20 2 20 2 20 2 20 V 2 20 2 20 Data Lookup & Reference I 3 20 3 20 2 3 253 214 3 20 3 20 3 20 3 20 J 4 20 4 20 4 523 4 20 Review 4 20 4 20 ӨГ K Math & Trig 4 5 20 100 5 20 5 20 5 200 5 20 5 20 V 5 20 L 6 20 More Functions 6 20 6 100 6 200 6 20 6 20 View 6 20 ... M 20 7 20 7 100 7 200 20 7 20 7 20 N Automate 8 20 8 20 8 100 8 200 300 8 20 8 20 8 20 Name Manager O 9 20 9 20 9 200 9 200 9 20 9 20 9 20 P 10 20 10 20 10 200 10 200 10 20 10 20 10 20 Tell me Define Name ✓ Use in Formula ✓ Create from Selection Q 11 20 11 20 11 200 11 200 11 20 11 20 11 20 R 12 20 12 20 12 20 12 200 12 20 12 20 12 20 S 13 20 13 20 13 20 13 20 13 20 T 13 20 14 20 100 13 14 20 100 14 20 100 14 20 14 20 100 14 20 100 14 20 U Tra Tra Re V J8 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 A INPUT Month Number 123 4567 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 B WAC Beginning Balance $100,000 fx C 9.500% CPR% D SMM% E WAM Mortgage PMT Expected F 360 Interest$ paid to Investors G PASS THROUGH RATE Scheduled Principal PMT H 9.000% Estimated Principal Pre-PMT POOL$ Total Principal paid to Investors $ J 100,000 Total Cash Flow to Investors K Pool Fees L PSA M 165 N O