Equally amortize an amount based on start and end date

[ANNA PANTELI]

My goal is to equally amortize the amount in cell L2 (see attached file) according to start date (cell A2) and clearance date (cell E2). In the example file amount is amortized manually (since I don't know how to do it) not using a formula, in range M2:R2.

 

[Khalid NGO]

Hi @ANNA PANTELI
Welcome to the forum ...

Couple of steps:
Enter this in M1:
=EOMONTH(A2,-1)+1

This in N1:
=IFERROR(IF(EOMONTH(M1,0)+1>$C$2,"",EOMONTH(M1,0)+1),"")
Copy across till you want

Do not change your formula in M2, Enter this in N2:
=IF(($L$2/$D$2)+SUM($M2:N2)<=$L$2,$L$2/$D$2,0)
Copy across...

Regards,

 

[Chihiro]

Alternately, you can use PMT function.
Need to store # of payments (i.e. # of Months) somewhere.

In this case it will be.
=PMT(0,6,L2) = -5162.24

0 = Interest Rate (in this case none)
6 = Number of payments
L2 = Starting debt (total cost)

Note that PMT will calculate amount deducted each month and will show negative amount. If you want + number you can do following.
=ABS(PMT(0,6,L2))
Or
=PMT(0,6,l2)*-1

 

[ANNA PANTELI]

Hi @ANNA PANTELI
Welcome to the forum ...

Couple of steps:
Enter this in M1:
=EOMONTH(A2,-1)+1

This in N1:
=IFERROR(IF(EOMONTH(M1,0)+1>$C$2,"",EOMONTH(M1,0)+1),"")
Copy across till you want

Do not change your formula in M2, Enter this in N2:
=IF(($L$2/$D$2)+SUM($M2:N2)<=$L$2,$L$2/$D$2,0)
Copy across...

Regards,

Dear Khalid,

Thank you so so so much! It worked perfectly!
Help much appreciated!

Regards
Anna