Question

Could someone help me fill out this spreadsheet? All the inputs are given

Data for   a representative single loan					n	n+1	n+2	n+3	n+4
Contractual cash flows					-100.00%	45.00%	37.00%	23.00%	10.00%
Gross IRR
Probability of default as %   of the remaining pool						2.00%	3.20%	3.80%	4.10%
% of the original pool that   has not defaulted
Loss given   default as % of amortized cost						22.00%	24.00%	25.00%	26.00%
Amortized cost/gross loan:   Representative single loan					n	n+1	n+2	n+3	n+4
Beginning balance
Interest income rate
+ Interest income
= Balance before receipt of   interest
Write-off rate
- Gross write-off of the   balance due before recoveries below
= Balance after write-off
- Collections from   non-defaulters
+ Reversal of write-off due   to recoveries
- Recoveries
= Ending balance
Allowance for bad debts:   Representative single loan						n+1	n+2	n+3	n+4
Beginning allowance balance
+ Bad debt expense: Derived
- Gross write-off of the   balance due before recoveries [derived above]
+ Reversal of write-off due   to recoveries
= Ending allowance balance   [UNDISCOUNTED METHOD]
Economic IRR
Portfolio of overlapping   loans: Gross loans					0	1	2	3	4	5	6	7
Beginning gross loans
Growth rate in new loans						3.00%	3.00%	3.00%	3.00%	3.00%	3.00%	3.00%
New loans					100000.00
+ Interest income
- Gross write-off of the   balance due before recoveries [derived above]
- Collections from   non-defaulters
+ Reversal of write-off due   to recoveries
- Recoveries
= Ending balance
Portfolio of overlapping   loans: Allowance					0	1	2	3	4	5	6	7
Beginning allowance balance
+ Bad debt expense: Derived
- Gross write-off of the   balance due before recoveries [derived above]
+ Reversal of write-off due   to recoveries
= Ending allowance balance   [UNDISCOUNTED METHOD]
Portfolio of overlapping   loans: Income statement					0	1	2	3	4	5	6	7
[Derived above] Interest   income from loans
[Derived above] (Bad debt   expense)
Interest expense as % of   beginning balance of deposits					4.00%	4.00%	4.00%	4.00%	4.00%	4.00%	4.00%	4.00%
(Interest expense on deposits   received)
= Net income
Dividend   payout rate if net income > 0					75.00%	75.00%	75.00%	75.00%	75.00%	75.00%	75.00%	75.00%
Portfolio of overlapping   loans: Balance sheet					0	1	2	3	4	5	6	7
Cash balance needed					0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
[Derived above] Gross loans					0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
[Derived above] (Allowance   for loan losses)					0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
= Total assets					0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
Deposits					0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
= Total liabilities					0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
Paid-in capital					0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
Retained earnings					0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
= Total equity					0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
Check					0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
Funding computations					0	1	2	3	4	5	6	7
Deposits as % of gross loans					80.00%	80.00%	80.00%	80.00%	80.00%	80.00%	80.00%	80.00%
Deposits
Cash as % of deposits					7.00%	7.00%	7.00%	7.00%	7.00%	7.00%	7.00%	7.00%
Cash balance needed
Deriving paid-in capital and   dividends					0	1	2	3	4	5	6	7
Cash balance needed
[Derived above] Gross loans
[Derived above] (Allowance   for loan losses)
[Derived above] (Deposits)
[Derived above] (Retained   earnings available)
= Level of paid in capital   needed
Cash flows					0	1	2	3	4	5	6	7
Interest received = Interest   income
(Interest paid)
= Operating cash flows
(Loans given)
Principal collections [Total   - interest received]
= Investing cash flows
Change in deposits
Change in paid in capital
(Dividends): Payout ratio *   Net income if net income >0
= Financing cash flows
= Net cash flows
Check					0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00