# Design a Loan Lender s Loan Margin Calculator Calculate the approximate margin required to cover costs and profits on a loan portfolio Use this sheet to estimate the by ijw53072

VIEWS: 45 PAGES: 4

• pg 1
```									                     Design a Loan - Lender's Loan Margin Calculator
Calculate the approximate margin required to cover costs and profits on a loan portfolio
Use this sheet to estimate the margin required. Then click on the "Design" tab to design the scheme.
Assume a specific sized batch (cohort) of loans is originated in year one and held for the life indicated.
The losses are assumed to take place in year one and reduce the number of performing cases by the same %.
The assumptions ignore any additional income and assume origination costs are funded separately.
Use View, Zoom for best screen size                  Change blue figures
Average case size           £50,000
Number of cases in batch (cohort)              1,000     completed in first year
Portfolio size     £50,000,000   at year end, then assumed static
Average life              5     years - earlier redemptions covered by fee
Fixed base administration costs pa            £100,000    per annum, per batch
Variable administration costs per case pa                £50     per case per annum
Losses expected per case over life               £750     1.50% of cases over 5 years
Percentage capital required                 2     % of case size: £1,000 per case
Return on capital required               15     % per annum
Profit required per case            £150     per annum -15% of £1,000 capital per case
Total costs & profit before losses pa           £300,000    per annum
Total costs & profit before losses over life        £1,500,000   over 5 years
Add overall losses         £750,000    over 5 years
Total overall costs, profits and losses         £2,250,000   over 5 years
Total overall pa       £450,000    per annum
Reduced number of performing cases                    985     reduced by loss %
Total overall cost, profit & losses per case            £457     per case per annum
Total margin including profit required                0.91%     pa with profit (91 basis points)
Approximate break-even margin                   0.61%     pa without profit (61 basis points)

Note - The capital required to support the lending depends on the risks and various other factors.
The simple calculation used here is used to estimate the profit per case. The percentage of capital required
is not so relevant to a Mutual lender, who could simply enter zero, but the admin costs might be higher.
A basis point is the same as one hundreth of a percent, and is used by banks instead of small percentages.

Created by Michael Kelly e-mail mkelly@foxwood.me.uk
Mortgage or Loan Product Design for the Lender
Design any loan product variables but ensuring the required return is achieved.
Enable Macros. Enter data (blue figures) and click button to recalculate selected variable so scheme IRR equals
the rate required. Start with a guess and ensure the Scheme IRR is near to the desired IRR. Round terms.
Then select a variable & recalcalulate it. Entering a nearer guess produces a more accurate calculation.
Use the example sheets shown on the tabs.
Basic Data                                                         Use View, Zoom for best screen layout
Wholesale average nominal borrowing rate               4.850       % pa
Rests per year on fund's borrowed                4         (Payment rate assumed the same)
Required nominal margin (Margin sheet shows 0.91%)               0.910       % pa to cover on-going expenses
Required nominal return              5.760       % pa
Minimum IRR required                5.8856        % pa Internal Rate of Return
Initial costs per case, eg processing costs         £250         (Include proc fee)
Average initial commission income or fees received           £200
Average full term         25          years
Average life           5          years
R for Repayment Mortgage, M for Mixed               i          Interest-only mortgage
£0
The Scheme
Rests per year (assuming monthly payments)               12         (Does not affect interest-only)
Initial interest rate        5.750       % pa
Changing after (if not, leave blank)                      months
To a new interest rate                        % pa
Changing again after                         months
To a new interest rate                        % pa
Initial cashback                      0.00%
Equivalent monthly commission              £0.00       per month
Redemption fee after 5 years             £50
Scheme IRR over 5 years                5.8979        % pa              HIGH, CAN REDUCE
Difference with desired return of 5.8856 %         0.0123                         SUFFICIENT

Notes on use: This sheet uses the 'Goal Seek' function, but you need some sensible guesses
to start with before clicking on a button. Set the easy variables first, like redemption fees, rests,
and known rates, for the average loan. Different LTV's might require different margins.
The calculation can accommodate three rate changes, monthly commissions and initial commissions,
redemption fee and cashback. The objective is to achieve a higher scheme IRR - indicated by the
red coloured messages. Check correct redemption charges by entering different life's and recalculating.
The Margin should cover both on-going costs, profit and provision for losses, but all initial front-end
costs can be entered as a separate lump sum figure. Initial costs should include any procuration fee.
Experiment to get used to the powerful design facilities available from this spreadsheet.
Mortgage or Loan Product Design for the Lender
Example of calculating optimum cashback on a mortgage
This is an example where the following figures are entered except the cashback, which is calculated
by pressing the 'Calc Cashback' button. Note this sheet is a result example only and does NOT calculate.

Basic Data                                                      Use View, Zoom for best screen layout
Wholesale average nominal borrowing rate             6.250       % pa
Rests per year on fund's borrowed               4         (Payment rate assumed the same)
Required nominal margin              0.750       % pa to cover on-going expenses
Required nominal return             7.000       % pa
Minimum IRR required                7.1859        % pa Internal Rate of Return
Initial costs per case, eg processing costs         £500         (Include proc fee)
Average initial commission income or fees received           £200
Average full term         10          years
Average life           5          years
R for Repayment Mortgage, M for Mixed                          Interest-only mortgage
£0
The Scheme
Rests per year (assuming monthly payments)                12        (Does not affect interest-only)
Initial interest rate        7.500       % pa
Changing after (if not, leave blank)                      months
To a new interest rate                        % pa
Changing again after                         months
To a new interest rate                        % pa
Initial cashback         £1,126       2.25%
Equivalent monthly commission               £5         per month
Redemption fee after 5 years            £50
Scheme IRR over 5 years                7.1859        % pa              EXACT MATCH
Difference with desired return of 7.1859 %          0.0000                         PERFECT

Note that the initial costs should allow for introducer's procuration fees.

Created by Michael Kelly e-mail mkelly@foxwood.me.uk
Mortgage or Loan Product Design for the Lender
Example of calculating the level interest rate for a consumer loan
This is an example where the following figures are entered except the initial interest rate, which is calculated
by pressing the 'Calc Init Rate' button. Note this sheet is a result example only and does NOT calculate.

Basic Data                                                      Use View, Zoom for best screen layout
Wholesale average nominal borrowing rate              6.250       % pa
Rests per year on fund's borrowed                4         (Payment rate assumed the same)
Required nominal margin               5.000       % pa to cover on-going expenses
Required nominal return             11.250       % pa
Minimum IRR required               11.7336        % pa Internal Rate of Return
Initial costs per case, eg processing costs          £400        (Include proc fee)
Average initial commission income or fees received            £50
Average full term          10         years
Average life            4         years
R for Repayment Mortgage, M for Mixed                r         Capital repayment mortgage
£0         i or blank for Interest-only
The Scheme
Rests per year (assuming monthly payments)               12         (Does not affect interest-only)
Initial interest rate       12.911       % pa
Changing after (if not, leave blank)                      months
To a new interest rate                        % pa
Changing again after                         months
To a new interest rate                        % pa
Initial cashback           £0         0.00%
Equivalent monthly commission               £0         per month
Redemption fee after 4 years            £150
Scheme IRR over 4 years                11.7336        % pa              EXACT MATCH
Difference with desired return of 11.7336 %          0.0000                         PERFECT

Note the margin should be sufficient to include an allowance for losses

Created by Michael Kelly e-mail mkelly@foxwood.me.uk

```
To top