tony
Posts: 136 
Likes: 91
|
Post by tony on Nov 5, 2015 16:17:33 GMT
I know little about spreadsheets but I have managed, by trial and error, to create one for recording my P2P investments. It contains a column showing the interest due to be paid to me each month - easy for platforms such as Savings Stream where interest only is returned. Can anyone provide me with a formula which will return the amount of interest plus capital expected each month for Funding Empire and the interest and service fee for platforms like Funding Circle?
|
|
|
|
Post by longjohn on Nov 5, 2015 16:31:01 GMT
For FC go to My Loan Parts and download the Repayments schedule file. No calculations required.
John
|
|
|
|
Post by parag on Nov 5, 2015 18:29:24 GMT
tony Our 'Lender Dashboard' provides you with this information on a loan and loan part level. If you want someone to point you in the right direction please drop us a line using the 'Contact Us' form and someone will get back to you. Regards, Parag
|
|
bigfoot12
Member of DD Central
Posts: 1,817
Likes: 816
|
Post by bigfoot12 on Nov 5, 2015 20:28:29 GMT
Good advice above, especially for funding circle which you can download.
Most sites have their own peculiar calculations and they are certainly not standard. Some don't reveal what they are, but people on site such as this have often worked them out. If you want to know exactly to the penny then you will need to use the correct equation. One thing the FCA could do that would be useful is insist that each company publishes all the interest rate equations they use with the conventions.
If you just want a rough number then LibreOffice Calc has the PMT function - I'm sure Excel has something similar:-
=PMT(Interest rate, loan length, -initial amount,0)
Assuming that your loan pays monthly then the rate must be a monthly rate and loan length be in months.
So 10% a year for 3 years on a £1,000 loan is 0.1/12 or 0.00833 per month for 36 months. The £1000 you are paying out and the interest you are receiving back so they have a different sign.
=PMT(0.1/12,36,-1000,0)=32.27
|
|
tony
Posts: 136
Likes: 91
|
Post by tony on Nov 7, 2015 20:31:01 GMT
Thanks for your help. I would like to split my column into two with one showing the interest due each month and the other the capital due each month - presumably the sum in each column will decrease each month as the capital is reduced. Are there formula's that will enable me to achieve this?
|
|
|
|
Post by ablrateandy on Nov 7, 2015 20:49:46 GMT
Assuming a loan starts on 1Jan15 and ends 1Jan18 at 10% amortising for £1,000 1. The PMT amount as above will be 32.27 February 1st : 2. The interest amount payable on 1Feb15 = £1,000 * (0.1/12) = £8.33 3. Therefore the capital repaid = 32.27 - 8.33 = 23.93 March 1st : 4. The interest amount payable is (1,000 - 23.93) * (0.1/12) = £8.13 5. Therefore the capital repaid = 32.27 - 8.13 = 24.13 etc etc etc tony |
|
tony
Posts: 136
Likes: 91
|
Post by tony on Nov 7, 2015 21:30:54 GMT
I understand your calculation and examples but I was hoping to find a formula which would automatically do the calculation and update the amounts each month without my having to do it! eg during the whole of November the columns would show the interest and capital I would expect to receive on December 1st and then on December 1st the columns would automatically show the amounts expected on January 1st. I have a spreadsheet which automatically adjusts figures according to the date but, although I eventually got it right, after sweating blood and tears, I now find it difficult to understand and edit as I get older.
|
|
|
|
Post by parag on Nov 7, 2015 21:54:35 GMT
tony Although not a spreadsheet, try www.theguardian.com/money/loan-repayment-calculator-interest-rates. This allows you to put in a capital amount, rate and term. It then gives you a loan repayment schedule by month and splits the capital and interest. As there is an element of rounding involved, you may see differences on some platforms (should be no more than 1p) on some lines. As most platforms handle rounding slightly differently you may see this small variance. The rounding online calculators use is very basic. Hope this helps a little.
|
|
|
|
Post by ablrateandy on Nov 7, 2015 21:57:55 GMT
That sounds a bit 3-dimensional if you want it for multiple loans. So you want to put each loan into a spreadsheet and then have a cumulative total?
If you post a spreadsheet of how you want it to look (without formulae) I will have a look at it.
|
|
tony
Posts: 136
Likes: 91
|
Post by tony on Nov 8, 2015 9:21:02 GMT
|
|
|
|
Post by ablrateandy on Nov 8, 2015 13:16:13 GMT
tony OK. Well it's doable. Questions : 1. Are all of your loans amortising or are some interest only? 2. How do you want to handle loans that are in default etc? 3. How many loans do you want this to cope with? 4. Do you want this to be a running tally of all future payments? (I would suggest "yes") It's complicated to do it right but I can do it this week, possibly tonight if my kids sleep.
|
|
tony
Posts: 136
Likes: 91
|
Post by tony on Nov 9, 2015 8:58:45 GMT
I have a separate sheet for each P2P and those which are interest only or interest less service charge I can manage. The one that I am struggling to work out is Funding Empire which amortizes all loans - I have nine loans in this platform. The platform does provide the information by showing forthcoming interest and capital repayments which are due, so, if I log on on the 1st of each month, I should see what I am due to be paid on different dates throughout the month (haven't tried this yet) However I would like to be able to verify the figures and view them, together with those for all other loans and platforms, from the one spreadsheet without having to log on to each platform each time. I haven't got around to thinking about bad debts - one thing at a time for this old wrinkly!
|
|
|
|
Post by ablrateandy on Nov 9, 2015 20:49:24 GMT
Hi tony . Please try the attachment. All of your information will be shown on sheet "Information Dashboard" ordered by the next due payment for each loan. (ie. as a payment gets made it moves down to the bottom of the queue again). To make it work, go into sheet Funding Empire and fill in the relevant columns for each loan. The columns should be self-explanatory : Contract Ref - I assume means the Loan Name or Number Interest Only or amortising - leave blank as I didn't build that in Updated Date - I assume is when you type in your comment. Please note 1. that I do NOT guarantee that this will tie up with any platform's figures although it is how Ablrate calculate it. It would be more helpful to you if someone from the relevant platform could state whether it works correctly or not. 2. It will cope with around 100 loans 3. I only built in amortising loans and the possibility to enter one platform for now Let me know if any questions  2015-11-09 Tony.xlsm (76.47 KB) |
|
tony
Posts: 136
Likes: 91
|
Post by tony on Nov 9, 2015 21:26:20 GMT
Thanks for that. Unfortunately I do not have Excel but use Planmaker which opens some Excel files but not this one. I also use Free File Opener but that will not open it.
Are you able to send it as a different Excel file format?
OK - I have now been able to open it and will try your suggestion and let you know how I get on. Thanks again for your valuable help.
|
|
|
|
Post by ablrateandy on Nov 10, 2015 6:37:41 GMT
|
|
