Go Ad-Free on CAclubindia with Daily Mobile Updates. Subscribe Now!

Prepaid Expense amortization

Adil (Junior Accountant) (27 Points)

10 June 2020

Hi All,

Can anyone help me out with this excel file. I want a formula which auto calculate according to the month between start date and end date.

NAME

START DATE

END DATE

NO.OF DAYS

AMOUNT

PER DAY

Jan-13

Feb-13

Mar-13

Apr-13

May-13

Jun-13

Jul-13

Aug-13

Sep-13

Oct-13

OFFICE RENT TAX '13-14

15-Jan-2013

14-Jan-2014

365

80,000.00

219.18

3506.849

Thanks.

Reply
Follow
Share

5 Replies

yasaswi gomes (My grammar is 💯 good I) (7290 Points)
Replied 11 June 2020

I have an example. Maybe you can check if this is working out.

If it doesn’t work out, enter manually and drag.

https://www.mrexcel.com/board/threads/prepaid-expense-amortization-formula.924171/

Upgrade to CAclubindia Pro. Subscribe Now!

Adil (Junior Accountant) (27 Points)
Replied 11 June 2020

Thanks, but this equation calculate according to month. I actually want if start date is on Jan 15 Calculate the remaining 16 days only, after Jan Feb 28 days and so on.

Adil (Junior Accountant) (27 Points)
Replied 11 June 2020

I tried this formula =IFS(AND(G1<$B2,G1<$C2),SUM(DAYS(EOMONTH($B2,0),$B2)*$F2),AND(G1>$B2,G1<$C2),(EOMONTH(G1,0))*$F$2) but this one only calculate the first month only.