Loan Payment Amounts

[fixmyprob]

I am trying to calculate the payments on a loan, where the interest is calculated daily and the first payment period is not equal with the remaining periods.

For example on a short term loan of 4 weeks, with weekly payments but the period form the start of the loan until the first payment is less than a week and I want equal payments over the term.

Any helps will be greatly appreciated. Cheers

 

[brettdj]

Option 1
1) Four end of week payments with payment depending on length of time
As you interest rate is daily but your payments are weekly then a two step process is to
a) Calculate the loan repayments if the loan was repaid daily using PMT
b) Convert the day payments into weeks using the FV equation

So if say there were 23 days on the loan (2 days and then 3 whole weeks), on a loan of $1000 at 5% interest compounding daily
daily repayments = PMT(5%/365,23,1000) = 43.55

End of week 1 payment
=-FV(5%/365,2,43.55)
= 87.11

End of week 2 payment (same as week 3 and week 4)
=-FV(5%/365,7,43.55)
=304.97

Option 2
I think for four equal payments based on day 2, 9, 16 and 23 you would need to goalseek using XNPV rather than solve algebraically. Will think about this further

Cheers

Dave

 

[fixmyprob]

Thanks, here is an examples see if you can solve it?

$100 for 17 days with 3 weekly payments and an interest rate of 480%.

Start 26 July
3 Weekly pmts starting 29 July finishing 12 August. Each payment should be $37.69, with a total repayment of $113.07.

I have a calculator which can do this but struggling to work out the formular?

Your help much appreciated!!

 

[xenou]

When I schedule this out I get interest of 13.70, not 13.07 (so 63 cents of unpaid interest remains).

Are you sure there's no "fine print". Do you know exactly how the rate is computed and what the compounding rules are. What exactly do you put into your "calculator" ...

-------------------------------------------------
| Day |      Date | PMT    | INTEREST |   VALUE |
-------------------------------------------------
|   1 | 26-Jul-11 |        |          | -100.00 |
|   2 | 27-Jul-11 |        | -1.32    | -101.32 |
|   3 | 28-Jul-11 |        | -1.33    | -102.65 |
|   4 | 29-Jul-11 | 37.69  | -1.35    |  -66.31 |
|   5 | 30-Jul-11 |        | -0.87    |  -67.18 |
|   6 | 31-Jul-11 |        | -0.88    |  -68.06 |
|   7 |  1-Aug-11 |        | -0.90    |  -68.96 |
|   8 |  2-Aug-11 |        | -0.91    |  -69.86 |
|   9 |  3-Aug-11 |        | -0.92    |  -70.78 |
|  10 |  4-Aug-11 |        | -0.93    |  -71.71 |
|  11 |  5-Aug-11 | 37.69  | -0.94    |  -34.97 |
|  12 |  6-Aug-11 |        | -0.46    |  -35.43 |
|  13 |  7-Aug-11 |        | -0.47    |  -35.89 |
|  14 |  8-Aug-11 |        | -0.47    |  -36.37 |
|  15 |  9-Aug-11 |        | -0.48    |  -36.84 |
|  16 | 10-Aug-11 |        | -0.48    |  -37.33 |
|  17 | 11-Aug-11 |        | -0.49    |  -37.82 |
|  18 | 12-Aug-11 | 37.69  | -0.50    |   -0.63 |
-------------------------------------------------
|     |           | 113.07 | -13.70   |         |
-------------------------------------------------

Interest formula here is [carrying value * (4.8/365)]. I've tried playing around with the first/last day of the period, using a 360 day convention, all to no avail.