Filetype Excel XLSX | Posted on 16 Aug 2022 | 3 years ago
Authentication
digital resources
201x Filetype XLSX File size 0.04 MB Source: www.intrum.com
Sheet 1: Examples Unsecured
| UNSECURED | |||||||||||||||||||
| Example | |||||||||||||||||||
| Invested amount | 100.0 | ||||||||||||||||||
| Gross collection | 200.0 | 2.0 | Cash Multiple | ||||||||||||||||
| Internal Rate of Return | 15.9% | ||||||||||||||||||
| Collection cost | 20.0% | 20.0% | 20.0% | 20.0% | 20.0% | 20.0% | 20.0% | 20.0% | 20.0% | 20.0% | 20.0% | 20.0% | 20.0% | 20.0% | 20.0% | ||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | Total | ||
| Cash flow distribution | 29.1% | 18.3% | 12.1% | 9.3% | 7.5% | 5.9% | 4.7% | 3.7% | 3.1% | 2.1% | 1.1% | 0.9% | 0.8% | 0.7% | 0.6% | 100.0% | Insert the cash collection profile from below | ||
| Gross collection | -100 | 58.2 | 36.6 | 24.1 | 18.6 | 15.1 | 11.8 | 9.3 | 7.5 | 6.1 | 4.3 | 2.3 | 1.9 | 1.6 | 1.4 | 1.2 | 200.0 | ||
| Net collection | -100 | 46.6 | 29.3 | 19.3 | 14.9 | 12.1 | 9.4 | 7.5 | 6.0 | 4.9 | 3.4 | 1.8 | 1.5 | 1.3 | 1.1 | 0.9 | 160.0 | ||
| Opening carrying amount | 100.0 | 69.3 | 51.0 | 39.8 | 31.2 | 24.1 | 18.5 | 13.9 | 10.2 | 6.9 | 4.5 | 3.5 | 2.5 | 1.6 | 0.8 | ||||
| Closing carrying amount | 69.3 | 51.0 | 39.8 | 31.2 | 24.1 | 18.5 | 13.9 | 10.2 | 6.9 | 4.5 | 3.5 | 2.5 | 1.6 | 0.8 | 0.0 | ||||
| Average carrying amount | 84.6 | 60.1 | 45.4 | 35.5 | 27.6 | 21.3 | 16.2 | 12.1 | 8.5 | 5.7 | 4.0 | 3.0 | 2.1 | 1.2 | 0.4 | 327.7 | |||
| Amortisation | -30.7 | -18.3 | -11.2 | -8.6 | -7.1 | -5.6 | -4.5 | -3.8 | -3.3 | -2.3 | -1.1 | -1.0 | -0.9 | -0.8 | -0.8 | -100.0 | |||
| Amortisation rate (amortisation/gross collection) | 52.7% | 50.0% | 46.4% | 46.1% | 47.2% | 47.6% | 48.5% | 50.3% | 53.7% | 54.5% | 48.0% | 50.7% | 55.1% | 61.0% | 69.0% | 50.0% | |||
| Front end | CF dist | 29.1% | 18.3% | 12.1% | 9.3% | 7.5% | 5.9% | 4.7% | 3.7% | 3.1% | 2.1% | 1.1% | 0.9% | 0.8% | 0.7% | 0.6% | 100.0% | Insert the profile in row 11 above and.. | |
| amortiz | 53% | 50% | 46% | 46% | 47% | 48% | 49% | 50% | 54% | 54% | 48% | 51% | 55% | 61% | 69% | 50.0% | read out the amortization here | ||
| Flatter Curve | CF dist | 20% | 16.0% | 13.0% | 11.0% | 10.0% | 8.5% | 6.5% | 5.0% | 3.7% | 2.5% | 0.8% | 0.8% | 0.8% | 0.7% | 0.7% | 100.0% | Insert the profile in row 11 above and.. | |
| amortiz | 47% | 46% | 46% | 47% | 51% | 54% | 55% | 57% | 59% | 61% | 45% | 51% | 57% | 62% | 71% | 50% | read out the amortization here | ||
| Flat | CF dist | 6.67% | 6.67% | 6.67% | 6.67% | 6.67% | 6.67% | 6.67% | 6.67% | 6.67% | 6.67% | 6.67% | 6.67% | 6.67% | 6.67% | 6.67% | 100.1% | Insert the profile in row 11 above and.. | |
| amortiz | 31% | 33% | 35% | 37% | 40% | 42% | 45% | 48% | 51% | 55% | 58% | 62% | 66% | 70% | 75% | 50.0% | read out the amortization here | ||
| SECURED | ||||||||||||||
| Example | ||||||||||||||
| Invested amount | 100.0 | |||||||||||||
| Gross collection | 160.0 | 1.6 | Cash Multiple | |||||||||||
| Internal Rate of Return | 13.4% | |||||||||||||
| Collection cost | 5.0% | 5.0% | 5.0% | 5.0% | 5.0% | 5.0% | 5.0% | 5.0% | 5.0% | 5.0% | ||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | Total | ||
| Cash flow distribution | 10.0% | 20.0% | 28.0% | 17.0% | 14.0% | 4.0% | 3.0% | 2.0% | 1.0% | 1.0% | 100.0% | Insert the cash collection profile from below | ||
| Gross collection | -100 | 16.0 | 32.0 | 44.8 | 27.2 | 22.4 | 6.4 | 4.8 | 3.2 | 1.6 | 1.6 | 160.0 | ||
| Net collection | -100 | 15.2 | 30.4 | 42.6 | 25.8 | 21.3 | 6.1 | 4.6 | 3.0 | 1.5 | 1.5 | 152.0 | ||
| Opening carrying amount | 100.0 | 98.2 | 80.9 | 49.2 | 30.0 | 12.7 | 8.3 | 4.9 | 2.5 | 1.3 | ||||
| Closing carrying amount | 98.2 | 80.9 | 49.2 | 30.0 | 12.7 | 8.3 | 4.9 | 2.5 | 1.3 | 0.0 | ||||
| Average carrying amount | 99.1 | 89.6 | 65.1 | 39.6 | 21.4 | 10.5 | 6.6 | 3.7 | 1.9 | 0.7 | 338.2 | |||
| Amortisation | -1.8 | -17.2 | -31.7 | -19.2 | -17.3 | -4.4 | -3.4 | -2.4 | -1.2 | -1.3 | -100.0 | |||
| Amortisation rate (amortisation/gross collection) | 11.3% | 53.9% | 70.8% | 70.8% | 77.1% | 68.4% | 71.7% | 74.5% | 73.9% | 83.8% | 62.5% | |||
| Rapid rise to Peak | IRR | 12.5% | ||||||||||||
| CF dist | 15.0% | 25.0% | 25.0% | 15.0% | 10.0% | 5.0% | 3.0% | 2.0% | 0.0% | 0.0% | 100.0% | Insert the profile in row 11 above and.. | ||
| amortiz | 40% | 65% | 73% | 73% | 76% | 76% | 78% | 84% | 0% | 0% | 66.7% | read out the amortization here | ||
| Gradual Rise | IRR | 11.10% | ||||||||||||
| CF dist | 10% | 20.0% | 28.0% | 17.0% | 14.0% | 4.0% | 3.0% | 2.0% | 1.0% | 1.0% | 100.0% | Insert the profile in row 11 above and.. | ||
| amortiz | 21% | 59% | 74% | 74% | 80% | 72% | 75% | 77% | 77% | 85% | 66.7% | read out the amortization here |
| REVALUATION | |||||||||||||||||||
| Example | |||||||||||||||||||
| Invested amount | 100.0 | ||||||||||||||||||
| Gross collection | 200.0 | ||||||||||||||||||
| Internal Rate of Return | 11.1% | ||||||||||||||||||
| Collection cost | 25.0% | 25.0% | 25.0% | 25.0% | 25.0% | 25.0% | 25.0% | 25.0% | 25.0% | 25.0% | 25.0% | 25.0% | 25.0% | 25.0% | 25.0% | ||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | Total | ||
| Cash flow distribution | 20.0% | 16.0% | 13.0% | 11.0% | 10.0% | 8.5% | 6.5% | 5.0% | 3.7% | 2.5% | 0.8% | 0.8% | 0.8% | 0.7% | 0.7% | 100.0% | Insert the cash collection profile from below | ||
| Gross collection | -100 | 40.0 | 32.0 | 26.0 | 22.0 | 20.0 | 17.0 | 13.0 | 10.0 | 7.4 | 5.0 | 1.6 | 1.6 | 1.6 | 1.4 | 1.4 | 200.0 | ||
| Net collection | -100 | 30.0 | 24.0 | 19.5 | 16.5 | 15.0 | 12.8 | 9.8 | 7.5 | 5.6 | 3.8 | 1.2 | 1.2 | 1.2 | 1.0 | 1.1 | 150.0 | ||
| Opening carrying amount | 100.0 | 81.1 | 66.1 | 53.9 | 43.4 | 33.2 | 24.1 | 17.1 | 11.5 | 7.2 | 4.2 | 3.5 | 2.7 | 1.8 | 0.9 | ||||
| Closing carrying amount | 81.1 | 66.1 | 53.9 | 43.4 | 33.2 | 24.1 | 17.1 | 11.5 | 7.2 | 4.2 | 3.5 | 2.7 | 1.8 | 0.9 | 0.0 | ||||
| Average carrying amount | 90.5 | 73.6 | 60.0 | 48.6 | 38.3 | 28.7 | 20.6 | 14.3 | 9.3 | 5.7 | 3.8 | 3.1 | 2.2 | 1.4 | 0.5 | 400.6 | |||
| Amortisation | -18.9 | -15.0 | -12.2 | -10.5 | -10.2 | -9.1 | -7.1 | -5.6 | -4.3 | -3.0 | -0.7 | -0.8 | -0.9 | -0.8 | -0.9 | -100.0 | |||
| Amortisation rate (amortisation/gross collection) | 47.3% | 46.9% | 46.8% | 47.8% | 50.9% | 53.3% | 54.4% | 56.1% | 57.8% | 59.1% | 45.8% | 50.9% | 56.5% | 60.6% | 67.5% | 50.0% | |||
| Assume Portfolio overperforms | |||||||||||||||||||
| Increase Gross Collections rises by 10% | |||||||||||||||||||
| IRR increase and this is reflected in our reporting systems | |||||||||||||||||||
| Called a revaluation within the range and not resulting in any P&L effect | |||||||||||||||||||
| Amortisation rates change slightly | |||||||||||||||||||
| Assume Portfolio overperforms heavily - and IRR would rise above "max" limit | |||||||||||||||||||
| Increase gross collections by 50% | |||||||||||||||||||
| IRR now needs to come down to "max" | |||||||||||||||||||
| Reset Book Value - invested amount in this example to lower IRR | |||||||||||||||||||
| This changed book value becomes a revaluation seen in the P&L | |||||||||||||||||||
| Amortization also changes in step with this |
no reviews yet
Please Login to review.
