Snasui Excel Forum

Hi dear,

I need your help how to calculate for ending balance of amortization amount.
For example
Amount 2000$ term :61months
so one month 2000/61=32.78$ is the amount clear monthly.

I need to know how do write formula to find first amount clear and end amount needed to clear.first amount may be equal monthly clear but how about end amount clear formula???
BTW outstanding if I choose Jan it should be 1967.22 but how about Feb,Mar,Apr...??? Which formula to archive this?I also need formula to mark status as Active or Closed.

Best wishes,

I guess,

1. F3
   =ROUND(E3/D3,2)
   Enter > Copy down
2. G3
   =E3-F3*(D3-1)
   Enter > Copy down
3. I3
   =F3
   Enter > Copy down
4. J3
   =MAX(0,E3-DATEDIF(B3,TODAY(),"m")*F3)
   Enter > Copy down
5. H3
   =IF(E3=J3,"Close","Active")
   Enter > Copy down

Thank you so much .it works

Hi Dear
Everything is fine but status is not right if I change End date to 10/30/2020 it is still active?


Thanks

Try Solving
=IF(J3=0,"Close","Active")

Thank