Fortnightly Loan Repayment (1 Viewer)

[pdrua]

Hello esteemed members.

I need some assistance with this. I am currently working on a loan management application with Access. The borrowers will repay the loan fortnightly. I am using the PMT function(=-PMT(Rate, Nper, Pv,[Fv],[type]) to calculate the repayment amount. I am stuck with how I will calculate the fortnightly repayment.

I will appreciate assistance from anyone who has solved a similar problem.

Thank you!

 

[GaP42]

Suggest looking at this video
Allow for 26 fortnights within a year.

Microsoft Access Loan Payment Calculator PMT Function - YouTube

 

[gemma-the-husky]

Offhand, I am not sure whether the interest rate you need touse is the annual rate, or the rate per period. If the latter, and you have 26 periods per year, then you would need to use a smaller interest rate. (Annual rate/26)

 

[ebs17]

Sub test_pmt()
    Dim p As Double, y As Long, py As Double, n As Long, a As Double
    p = 5
    y = 26
    n = 26
    a = 10000
    py = ((1 + (p / 100)) ^ (1 / y)) - 1
    Debug.Print "percentage per year (p): " & p
    Debug.Print "number of payments per year (y): " & y
    Debug.Print "pro rata percentage on rate per year (py): " & py
    Debug.Print "number of payments (n): " & n
    Debug.Print "loan amount (a): " & a
    Debug.Print Pmt(py, n, a) * -1
End Sub

 

[Pat Hartman]

The video was pretty clear on how to do this. My only quarel would be regarding hardcoding the number of payments. Using the same table, I would add an additional column for the 26. If 26 is the norm, then you can make it the default so you only have to enter cases where the number of payments is different.

 

[courtsmammouth]

Hello esteemed members.

I need some assistance with this. I am currently working on a loan management application with Access. The borrowers will repay the loan fortnightly. I am using the PMT function(=-PMT(Rate, Nper, Pv,[Fv],[type]) to calculate the repayment amount. I am stuck with how I will calculate the fortnightly repayment.

I will appreciate assistance from anyone who has solved a similar problem.

Thank you!

Sure, contact me now!

 

[gemma-the-husky]

This is how it works.

MsgBox -Pmt(0.002, 26, 1000)

(repayment = -Pmt(0.002, 26, 1000) stores the value in a variable)

0.002 is 0.2% interest per period
26 is number of periods (therefore 5.2% pa interest
1000 is capital

returns approx 39.51

MsgBox -Pmt(0.00, 26, 1000)
No interest, so 26 repayments, should be 1000/26

returns approx 38.46

 

[ebs17]

5.2% pa interest / 26 periods => 0.2% interest per period ??
This is mathematically wrong because the compound interest effect is suppressed, but it is good for the lender because it is advantaged.

 

[gemma-the-husky]

5.2% pa interest / 26 periods => 0.2% interest per period ??
This is mathematically wrong because the compound interest effect is suppressed, but it is good for the lender because it is advantaged.

I doubt that's right. The PMT formula must take the compound interest effect into account.

It doesn't matter whether it's 26 years at 0.2% per annum or 26 fortnights at 5.2% per annum. I'm sure if you run the calculations in a spreadsheet it will work out correctly.

Annuities, NPVs etc, are all related.


Edit. I just checked this.

I just constructed an excel table, and it actually evaluates correctly, assuming the interest is calculated on the balance at the start of a period, and the repayment at the end.

So Loan £1000, interest at 0.2% for period 1 = £2
Repayment of £38.51, gives a balance at the start of Period 2, of £962.49

New Balance £962.49, interest at 0.2% for period 2 = £1.92
Repayment of £38.51, gives a balance at the start of Period 3, of £924.90

Because the amount of £38.51 is rounded, this actually produces an overpayment of 4p at the end of 26 periods, and a total interest of £27.22

But the calculation definitely takes into account a compounding formula.


If the deal is £1000 loan, simple interest of 5.2% for the year, interest of £52, then you get 26 repayments of £40.46, and it's a different formula. ie not the PMT formula. Doing it this way you get an APR of roughly double the 0.2%. It actually works out at 0.38%. That's why a hire purchase quote of, say 7%, actually works at a real (APR) interest rate of about 14%.