|
|
Post by gingergent on Dec 24, 2013 15:04:01 GMT
Has anyone managed to replicate the FC payment schedule calculations? I'm attempting to generate projected cashflows for loans from principal, term and rate. I've started with a simple example, and I'm having trouble tying out.
I'm ignoring FC's rounded rate for the overall loan and instead using the actual volume-weighted average of the individual rate bands. I'd assumed the headline rate was achieved from monthly compounding, but I now suspect they're running bond-style 1/12th rate/month and there's some rounding that I'm not accounting for (or my maths is out). I've not been able to find anything definitive on the FC site. All FC repayments for my 'sample loan' are equal value and the sum of the bands matches the headline loan size. Any ideas?
Principal: £90K, 36 month from 24/7/2012, FC repayments £2,853.39 (loan 1365)
FC 'rate': 8.8%, Avg rate: 8.79626667%
Simple rate (1/12th rate per month)
Monthly rate=0.7330222222%
Closing balance=£2.45
Ideal repayment=£2853.45 (+£0.06)
Simple rate (1/12th rate per month), outstanding amount truncated to 2dp each month
Monthly rate=0.7330222222%
Closing balance=£2.24
Ideal repayment=£2853.45 (+£0.06)
Monthly compounding (r^(1/12) per month):
Monthly rate=0.7050306720%
Closing balance=£-569.99
Ideal repayment=£2839.43 (-£13.96)
Daily compounding, ACT/365:
Daily rate=0.0231004305%
Closing balance=£-550.72
Ideal repayment=£2839.90 (-£13.49)
Daily compounding, ACT/ACT:
Daily rate=0.0230373073% (2012) \ 0.0231004305% (2013-2015)
Closing balance=£-561.21
Ideal repayment=£2839.64 (-£13.75)
|
|
|
|
Post by gingergent on Dec 24, 2013 16:07:54 GMT
Removed
|
|
|
|
Post by jevans4949 on Dec 26, 2013 12:37:28 GMT
I'm not a participant in FC, but I wrote a program to calculate APR and repayments when I worked for a major UK bank when the legislation requiring APR calculation was first introduced in the 1970's. (Prior to that, the bank used printed mathematical tables to calculate repayments).
There is a formula (fairly complex, and I can't remember it now) for calculating what repayments should be, but I validated this and found you needed to make fine adjustments to get the optimal repayment; I did this using a Newton-Raphson type procedure. (At one point, when testing the limits of parameter values, I managed to lock up an IBM 360/65 computer for more than half a minute.)
If the loan is based on compound interest, and the lender applies the interest to the account on fixed calendar dates (rather than dates based on the drawdown date, then the calculation (and the APR calculation that then arises) cannot be wholly accurate unless the drawdown date is known; you would also need to forecast how the fixed dates were affected by weekends and bank holidays. The bank in question actually calculated interest and charges on one fixed day each month or each quarter and applied them a few days later.
In the end we didn't worry too much, as most compound-interest loans were charged on a variable rate above Bank of England base rate, which in those days was likely to change several times during a year.
EDIT: If you have the dates and values of all repayments, and interest and charges, you can calculate the APR, as defined in the EU including the UK, using the spreadsheet irr function
EDIT 2: If you have what used to be called a Personal Loan, where flat rate interest based on the initial principal over the term of the loan is applied, the calculation of repayments is far simpler. The APR is then around double the quoted flat rate.
|
|
|
|
Post by gingergent on Dec 26, 2013 17:05:11 GMT
There is a formula (fairly complex, and I can't remember it now) for calculating what repayments should be Yep, I've tried both manual search using a spreadsheet and using the formula from Wikipedia's Mortgage_calculator page, which reassuringly but unfortunately agree. If the loan is based on compound interest, and the lender applies the interest to the account on fixed calendar dates (rather than dates based on the drawdown date, then the calculation (and the APR calculation that then arises) cannot be wholly accurate unless the drawdown date is known; you would also need to forecast how the fixed dates were affected by weekends and bank holidays I'll have to play about and see whether it's down to treatment of weekends\holidays. I think the accrual start date is actually earlier than I'd believed; the loan summary includes the acceptance date, but the FC agreements state: which makes things more difficult and will make my current calculations more errant not less (since the closest I've got already has the borrower apparently underpaying by £2.45)... I'm going to try to reconcile using another loan that's not yet made any payments, since FC provide a personal forward cashflow schedule broken down into principal and interest but historical data seems to be only available split across monthly statement downloads. On a small investment the rounding errors might be so large as to be prohibitive, so if that doesn't help I may just have to ask FC to clarify.
|
|
maxmarengo
Member of DD Central
Posts: 96 
Likes: 28
|
Post by maxmarengo on Dec 29, 2013 9:14:19 GMT
Hi gingergent, I may not be understanding your difficulty, but I have found I can replicate the FC repayments of interest and principal using the Excel functions IPMT and PPMT. On an individual loan this gets you "close enough" (FC have some tweaks for rounding pence up and down). These are in the financial functions add in. Use months, with the interest rate as the FC rate/12. I have attached what I get for the example you used. It may be that you are trying to be much more accurate than me - an error of £2.45 on a £90,000 loan would not concern me. However, I think my method gets it to within £1. FCLoanBookSimulate Example.xls (58.5 KB) |
|
|
|
Post by gingergent on Dec 29, 2013 20:19:05 GMT
Thanks Max. I think I may just be trying to be 'too accurate'. I'm less worried about the residual balance than about the discrepancy in calculated monthly payment, since my ultimate interest is in doing some analysis that will depend on the cashflows. As above, I can either use FC cashflows and get a residual or use my calculated cashflows and get a zero cloing balance. I would use the FC cashflows, but they aren't published on a per-loan basis.
Interestingly, the Excel approach gives paydown on the correct date (as you'd expect) but uses yet another monthly payment to achieve it: £2853.61 (D15+D16, etc). It's reassuring that you're also working on rate/12 per month, so I think I'll go with my current 'simple rate' approach (which sounds like it should mirror IPMT and PPMT). I think there's something 'interesting' going on with day conventions that's throwing my numbers, but I doubt I'll fathom it until I can get FC to just tell me. It's probably not even close to a material difference.
|
|
