Tweet
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.
Concept of Loan Amortization - allaboutFORUMS
As most of you know, I am more of a technical/mathematical specialist than financial expert, so I apologise if it’s 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:
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
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:
Let’s 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:
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 apportionment factors in separating the figures.
Attachment pdf A goes here
Our SS-18000-AAD-A.pdf
Attachment pdf B goes here
Our SS-18000-AAD-B.pdf
Attachment pdf C goes here
Our SS-18000-AAD-C.pdf
The next spreadsheet illustrates this 2 loan concept in the form of a typical Loan Statement
Statement Type SS goes here
Our SS-18000-AAD-Statement Type View.xls
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.
Concept of Loan Amortization - allaboutFORUMS
As most of you know, I am more of a technical/mathematical specialist than financial expert, so I apologise if it’s 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 an 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 an 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
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)
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%
Let’s 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
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 apportionment factors in separating the figures.
Attachment pdf A goes here
Our SS-18000-AAD-A.pdf
Attachment pdf B goes here
Our SS-18000-AAD-B.pdf
Attachment pdf C goes here
Our SS-18000-AAD-C.pdf
The next spreadsheet illustrates this 2 loan concept in the form of a typical Loan Statement
Statement Type SS goes here
Our SS-18000-AAD-Statement Type View.xls
Comment