Calculating Remaining Loan Balance with Formilas?

[MichaelMutz]

I am trying to figure out a way to show the CURRENT balance of a loan for properties (kind of building an output for an amortization table to get the remaining balance at any given time) in Monday but I am having quite a bit of trouble with it.

I am using this link below as a kind of building block for it.

I have these columns so far for inputs:

• Initial Loan Balance
• Loan Start Date
• Paid Months (with this formula -[ROUNDDOWN(DAYS(TODAY(),{Loan Start Date})/(365/12),0)] )
• Interest Rate,
• Payment Periods,
• Payment Amount

[MichaelMutz]

I built a way to do this – for anyone that wants it - you need these columns for it. IF you name them these names, you can copy my formulas and paste them in and then after that just revery them back to whatever names you want!

Type of Column for each of these:
Start Date: Date column
Months Paid: Formula column
Loan Amount: Numbers column
Interest Rate: Numbers column
Monthly Payment: Formula column
Remaining Balance: Formula column
Term (Months): Numbers column

**It will matter for how it calulcates – match my formatting (Interest rate needs to show as a % etc.)

Formula for “Months Paid”:
ROUNDDOWN(DAYS(TODAY(),{Start Date})/(365/12),0)

Formula for “Monthly Payment”:
ROUND( ( {Loan Amount} * ( {Interest Rate} / 12 ) ) /
( 1 - POWER( 1 + ( {Interest Rate} / 12 ), -{Term (Months)} ) ),2)

Formula for “Remaining Balance”:
ROUND({Loan Amount} * POWER(1 + {Interest Rate} / 12, {Months Paid}) - {Monthly Payment} * (POWER(1 + {Interest Rate} / 12, {Months Paid}) - 1) / ({Interest Rate} / 12), 2)

[MichaelMutz]

@Matias.Monday – Is there any way to correct this title so it is easier for people to search if they want info for how to do it? Just want to make it:

Calculating Remaining Loan Balance with Formulas