• Welcome to the LegalBeagles Consumer and Legal Forum.
    Please Register to get the most out of the forum. Registration is free and only needs a username and email address.
    REGISTER
    Please do not post your full name, reference numbers or any identifiable details on the forum.

Understanding PPI Calculations--- The Concept of 2 Loans & Basic Spreadsheets

Collapse
Loading...
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Understanding PPI Calculations--- The Concept of 2 Loans & Basic Spreadsheets

    Understanding PPI Calculations---The concept of 2 Loans and Basic Spreadsheets


    Firstly, I would like you to be familiar with the concept of Loan Amortization and Amortization Schedules-so for now-please study the thread below - if you have not already done so.

    Understanding PPI Calculations--- Loan Amortization

    As most of you know, I am more of a technical/mathematical specialist than financial expert, so I apologise if its a grandmother..eggs scenario and apologies to any financial purists if I use Laymen's Terms rather than Financial Terms, but this thread forms the basis for the more complex threads later on, where we will calculate the figures on both Active Loans and Settled Loans.

    I have devised various scenarios on these spreadsheets, and continually bounced them off my mate Bill-K, who has a far greater intuitive grasp of the interest principles and associated formulae than I have, resulting in frequent enhancements and modifications. I usually have a custom one for complex cases, and include facilities for extra charges & refunds etc.

    I will summarize all that has gone before and explain everything in detail with a full audit trail, in an attempt to bring everybody to the same level of understanding on how these single premium PPI loans actually work in practice—rather than summarizing it, and you merely accept what I say without actually understanding the audit trail.

    Later, in post number 2 , I will also introduce the basic kernel which will form the backbone of the more complex spreadsheets that we will be using in the later threads, ----and for those not used to working with spreadsheets, I will attempt to convince you that PPI Calculations are just made for spreadsheets.


    First of all, we need the details from the original Loan Agreement– an example is illustrated below with the figures modified to the ones I am going to use for a 2 yr loan (just right for Screen Prints!) :




    Summarizing the details:
    • Cash Advance Loan of £15,000 at a initial Apr of 10.01% apr resulting in mthly payment of £688.99 pm
    • PPI Single Premium Loan of £3,000 at a initial Apr of 10.01% apr resulting in mthly payment of £137.80
    • Total of Both Loans of £18,000 at a initial Apr of 10.01% apr resulting in mthly payment of £826.79
    • Date of Agreement of 1/4/2008 with first payment on 1/5/2008
    • Term of Loan 24 months
    • Additionally—lets assume an increase in the APR at 1/1/2009 resulting in APR 11.56% and revised total mthly payment of £834.79
    The Concept of 2 separate individual Loan Accounts

    First of all we need to understand how these loans work in practice.
    The most important concept to grasp on this subject is

    that there are always 2 loans effectively in operation, even though you may receive only one summarized statement and one debited payment from your bank account
    • Loan for Cash Advance (might be re financed -but not relevant in this discussion)
    • Loan for a Single Premium for Payment Protection Insurance (usually then paid to third parties)
    What we have to do is be aware of this and able to apportion any figures quoted into the relevant loans.

    As the APR is the same on both loans - first of all we need to calculate the ratios of how the premiums are divided at outset, and similarly throughout the life of the overall loan.

    In the example I am using the ratios Loan Amount to the Total Loan Amount resulting in these 2 ratios:
    • Cash Advance Loan-£15,000 to £18,000 = £15,000 divided by £18,000 = 83.33%
    • PPI SPI Loan - £3,000 to £18,000 = £3,000 divided by £18,000 = 16.67%
    As I develop the explanations further, I will be using these "percentages" and call them Apportionment Factors.
    Lets just assume for a moment that we have a nice simple case of no PPI refunds, a completed loan run its full course with no Settlement Payment (we will bring these into play in the next 2 threads) or even an active loan with no PPI refunds.


    So if we look at our Loan Statements--the actual:
    • Interest
    • Payments
    • Outstanding balance
    can always be split into the 2 separate Loans by using the above Apportionment Factors.

    There will be added complications later on the treatment of Settlement Payments after a PPI refund, which attracts a lower Apportionment Factor--but lets leave that until we get there--and just concentrate on the basics for now.

    Below is the summary of the total loan + the cash loan + the PPI loan

    Double click all 3 in succession then page between all 3 to see the concept of 2 loans & use of the apportionmement factors in separating the figures.




    The next spreadsheet illustrates this 2 loan concept in the form of a typical Loan Statement

    Last edited by Turboman; 12th October 2010, 14:25:PM.

  • #2
    Re: Understanding PPI Calculations--- The Concept of 2 Loans & Basic Spreadsheets

    This spreadsheet is a simple method of calculating the implications of an ACTIVE or SETTLED loan with PPI included, if successfully deemed to be mis-sold.

    I've basically stripped our main SS down to demonstrate the basic techniques we shall use in the more complex cases in Active & Settled Loans later on.

    So-ignore Refunds & Rebates till later and experiment with just amending the figures in green--especially the "Number of payments made so far"--which has to be entered to activate the SS--enter 24 first which would be a loan gone to full term--then try 13 which is the date that we shall use in next thread to calculate refunds and the figures for the "restructured Loan".

    See how easy it is to get the refund figures--this is the power in Excel (or OO-Calc) that is especially suited to these PPI sums.

    In the previous SS in post 1, I introduced a rate change--to cater for this (and missed payments) simply overwrite the green column "Monthly Amount Paid" (it mucks up the formulae when you do this by the way for a subsequent loan case--SO make sure you save this one as a master somewhere first.

    The SS calculates 8% just like the theory in the Bank Charges SS that Budgie has filed here on Legal Beagles.

    Last edited by Turboman; 12th October 2010, 14:31:PM.

    Comment


    • #3
      Re: Understanding PPI Calculations--- The Concept of 2 Loans & Basic Spreadsheets

      Right finished this thread--I hope you didn't find it too rambling and useful.

      Your comments would be greatly appreciated--Positive or negative

      Turbo

      Comment


      • #4
        Re: Understanding PPI Calculations--- The Concept of 2 Loans & Basic Spreadsheets

        The spreadsheet is really useful. I can easily use it to give me a quick indication of my PPI claim :-)

        Comment

        View our Terms and Conditions

        LegalBeagles Group uses cookies to enhance your browsing experience and to create a secure and effective website. By using this website, you are consenting to such use.To find out more and learn how to manage cookies please read our Cookie and Privacy Policy.

        If you would like to opt in, or out, of receiving news and marketing from LegalBeagles Group Ltd you can amend your settings at any time here.


        If you would like to cancel your registration please Contact Us. We will delete your user details on request, however, any previously posted user content will remain on the site with your username removed and 'Guest' inserted.
        Working...
        X