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

More Info
									                     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 loan advance          £50,000
                                              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 loan advance          £50,000
                                           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 loan advance           £5,000
                                           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