Loan Amount Formula

[Seeka]

Hi there
I'm looking for a loan formula that calculates the loan amount based an a payment schedule that is irregular. For example if you have a loan amount of £10,000 over 5 years at 7% giving a monthly payment of £242.72, I need formula that calculates what would happen to the £10k initial loan if you made the payments £250 in year one, £240 in year 2 etc. PV doesn't seem to work with irregular payments and I've tried to use XNPV but it doesn't return accurate data. I'd appreciate any help, I've been Googling for hours! Many thanks

 

[Seeka]

Hi. Thank you for all your help so far. I have been using the following formula (thank you joeu2004!) to construct the spreadsheet;

Payment =SUM(B5-B7/(1+B3/B22)^B19)/((1-(1+B3/B22)^(-(B20-B15)))/(B3/B22)+B15)

Whereby;
B3= Rate
B5= Cost
B7= Balloon/RV(Residual Value)
B15= # of advance payments
B19= total # of payments
B20= total # of payments
B22= # payments per year

This works fine for monthly but is slightly out when calculating quarterly payments.
Please can anyone help me adjust the above formula to get a more accurate result on a quarterly profile?

NB If you’re wondering why “total # of payments” is duplicated in cells B19 & B20, in my larger spreadsheet there is an option to have a 60 month (for example) term with 3 advance payments as either a 3+57 or a 3+59 and so cells B19 & B20 could have different values as follows

	B19	B20
3+57	60	60
3+59	60	62

<tbody>
</tbody>

Using B19 & B20 in this way achieves the desired results for monthly profiles and I’ve removed the option for users to select more than one in advance on quarterly profiles so this doesn’t have any bearing on my issue of quarterly payments not calculating correctly

I’ve attached an example of a section of my spreadsheet here;
https://app.box.com/s/u7ztbaryys344z7gzqxm4xmvwhqtn6uk

To explain again what I’m trying to achieve, the purpose of this sheet is to first calculate a payment in B11 (by entering data in the bold outlined cells), and then to gradually adjust the payments month by month in column D to see the affect it has on the rate, cost and RV in cells D30:E35. Before the payments in column D are adjusted you’d expect the rate, cost and RV in cells D30:E35 to match the rate, cost and RV in cells B3, B5 & B7. If you select “monthly” in cell B9 the figures do indeed always match but if you change B9 to “quarterly” the figures are a fraction out

Many thanks
Jon