calculating principal and interest and amount remaining

Question

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.

Answer 1

So it bugged me enough to work it out.

The Loan Details work out to:

Where the Bi-weekly interest rate is =$D$5/$D$2 (=IntRate/LoanTerm)

Then, the table itself works out to:

With the formulas, copied down the column:

• in Column J, starting in J4: =J3-I4
• in Column K, starting in K4: =I4-L4
• in Column L, starting in L4: =J3*$D$7