Hello,
I'm having trouble with modeling CashFlow for a Loan portfolio from a Lender's perspective.
Example:
Example Assumptions:
Average issued loan amount: 1000
Average term: 5 months
Annual interest rate: 6%
Monthly administrative fee: 0.5% from initial issued loan amount
Default rate: 5%
All revenue reveived from loans (monthly payments) are reissued to new loans.
Questions:
1. Is it possible to make the table more maneageable, by using some formulas (maybe FV?)? If I make it for 60 months, it will be too big
2. How to deduct defaults from the income stream?
I'm having trouble with modeling CashFlow for a Loan portfolio from a Lender's perspective.
Example:
| Book1 | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | Loan amount added monthly | 1000 | |||||||||||
| 2 | Interest rate | 6% | |||||||||||
| 3 | Admin fee per month | 0,5% | |||||||||||
| 4 | Average term months | 5 | |||||||||||
| 5 | |||||||||||||
| 6 | Month 1 Cash flow | Month 2 Cashflow | Month3 Cash flow | Month 4 Cashflow | Month 5 Cash flow | Total Cashflow | |||||||
| 7 | Month | Added to loan portfolio | Reinvestment of payments received | Total Loans given out | Total payment received (Principle + interest + Admin fee | Total payment received (Principle + interest + Admin fee | Total payment received (Principle + interest + Admin fee | Total payment received (Principle + interest + Admin fee | Total payment received (Principle + interest + Admin fee | ||||
| 8 | 1 | 1000 | 0 | 1000,00 | 0,00 € | ||||||||
| 9 | 2 | 1000 | 208,01 € | 1208,01 | 208,01 € | 208,01 € | |||||||
| 10 | 3 | 1000 | 459,29 € | 1459,29 | 208,01 € | 251,28 € | 459,29 € | ||||||
| 11 | 4 | 1000 | 762,83 € | 1762,83 | 208,01 € | 251,28 € | 303,55 € | 762,83 € | |||||
| 12 | 5 | 1000 | 1 129,52 € | 2129,52 | 208,01 € | 251,28 € | 303,55 € | 366,69 € | 1 129,52 € | ||||
| 13 | 208,01 € | 251,28 € | 303,55 € | 366,69 € | 442,96 € | 1 572,48 € | |||||||
| 14 | 251,28 € | 303,55 € | 366,69 € | 442,96 € | 1 364,47 € | ||||||||
| 15 | 303,55 € | 366,69 € | 442,96 € | 1 113,20 € | |||||||||
| 16 | 366,69 € | 442,96 € | 809,65 € | ||||||||||
| 17 | 442,96 € | 442,96 € | |||||||||||
| 18 | |||||||||||||
Sheet1 | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B8:B12 | B8 | =$B$1 |
| D8:D12 | D8 | =B8+C8 |
| C9 | C9 | =E9 |
| C10 | C10 | =E10+F10 |
| C11 | C11 | =E11+F11+G11 |
| C12 | C12 | =E12+F12+G12+H12 |
| J8:J17 | J8 | =SUM(E8:I8) |
| E9:E13 | E9 | =(PPMT($B$2/12,$A8,$B$4,-$D$8,0))+(IPMT($B$2/12,$A8,$B$4,-$D$8,0))+($D$8*$B$3) |
| I13:I17 | I13 | =(PPMT($B$2/12,$A8,$B$4,-$D$12,0))+(IPMT($B$2/12,$A8,$B$4,-$D$12,0))+($D$12*$B$3) |
| F10:F14 | F10 | =(PPMT($B$2/12,$A8,$B$4,-$D$9,0))+(IPMT($B$2/12,$A8,$B$4,-$D$9,0))+($D$9*$B$3) |
| G11:G15 | G11 | =(PPMT($B$2/12,$A8,$B$4,-$D$10,0))+(IPMT($B$2/12,$A8,$B$4,-$D$10,0))+($D$10*$B$3) |
| H12:H16 | H12 | =(PPMT($B$2/12,$A8,$B$4,-$D$11,0))+(IPMT($B$2/12,$A8,$B$4,-$D$11,0))+($D$11*$B$3) |
Example Assumptions:
Average issued loan amount: 1000
Average term: 5 months
Annual interest rate: 6%
Monthly administrative fee: 0.5% from initial issued loan amount
Default rate: 5%
All revenue reveived from loans (monthly payments) are reissued to new loans.
Questions:
1. Is it possible to make the table more maneageable, by using some formulas (maybe FV?)? If I make it for 60 months, it will be too big
2. How to deduct defaults from the income stream?
