calculating principal and interest and amount remaining

loan amount 4000
term in month 24
repyament freq forthnightly
repayment amount 93.29
annual interest rate 12%


Loan Details excel:

D1: Loan Amount: 4000
D2: Loan Term (months): 24
D3: Repayment Frequency: fortnightly
D4: Repayment Amount: 93.29
D5: Annual Interest Rate: 12%

In excel table header
G1: account_number
H1: repayment_date
I1: repayment_amount
J1: amount_remaining
K1: Principal
L1: Interest



account number | repayment date | repayment amount | amount remaining | Principal | Interest |
| -------------- | -------------- | ---------------- | ---------------- | --------- | -------- |
| 123 | 1-Jun-22 | 93.29 | | | |
| 123 | 15-Jun-22 | 93.29 | | | |
| 123 | 29-Jun-22 | 93.29 | | | |
| 123 | 13-Jul-22 | 93.29 | | | |
| 123 | 27-Jul-22 | 93.29 | | | |
| 123 | 10-Aug-22 | 93.29 | | | |
| 123 | 24-Aug-22 | 93.29 | | | |
| 123 | 7-Sep-22 | 93.29 | | | |
| 123 | 21-Sep-22 | 93.29 | | | |
| 123 | 5-Oct-22 | 93.29 | | | |
| 123 | 19-Oct-22 | 93.29 | | | |
| 123 | 2-Nov-22 | 93.29 | | | |
| 123 | 16-Nov-22 | 93.29 | | | |
| 123 | 30-Nov-22 | 93.29 | | | |
| 123 | 14-Dec-22 | 93.29 | | | |
| 123 | 28-Dec-22 | 93.29 | | | |
| 123 | 11-Jan-23 | 93.29 | | | |
| 123 | 25-Jan-23 | 93.29 | | | |
| 123 | 8-Feb-23 | 93.29 | | | |
| 123 | 22-Feb-23 | 93.29 | | | |
| 123 | 8-Mar-23 | 93.29 | | | |
| 123 | 22-Mar-23 | 93.29 | | | |
| 123 | 5-Apr-23 | 93.29 | | | |
| 123 | 19-Apr-23 | 93.29 | | | |
| 123 | 3-May-23 | 93.29 | | | |
| 123 | 17-May-23 | 93.29 | | | |
| 123 | 31-May-23 | 93.29 | | | |


what i need to calculate is the amount remaining and pricipal and interest for each repayment date in excel i have tried a few things but it doesnt work note the repayment amount should not change please if someone can help me calculate i tried getting some information


K2 (Principal): =PPMT(D5/24,ROW()-1,D2,-D1)
L2 (Interest): =IPMT(D5/24,ROW()-1,D2,-D1)
J2 (Remaining Amount):

but this does not work for me when i try to use this formula =PPMT(D5/24,ROW()-1,D2,-D1) it gives me an error saying something is wrong in the formula.
please find attached:
sample data.xlsx

Welcome to the forum.

1. Change ALL HTML tags in your post to CODE tags.
2. There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

The cells in question contain different formuale to the ons=es in your post. J2 will accept the formula you mention and returns a result, however I have no idea what results you are expecting.

There is nothing inherently 'wrong' with the formula.