Prepaid Expense amortization

[adiles]

Hi All,

Can anyone help me out with this excel. 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

OFFICE RENT TAX '19-20

15-Jan-2013​

14-Jan-2014​

365​

80,000.00

219.18

3506.849​

Auto fill

Auto fill

Auto fill

I tried using 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 it wont work for me.
Your reply will be highly appreciated.

 

[gaz_chops]

Use the data from your 1st post to post a new example of how you want the Yearly data to be shown.

 

[adiles]

NAME	START DATE	END DATE	NO.OF DAYS	AMOUNT	PER DAY	2013	2014	2015
OFFICE RENT TAX '19-20	15-Jan-2013​	14-Jan-2015​	730​	80,000.00	109.59	38,356.16	40,000.00	1,643.84

 

[gaz_chops]

So you want it shown as Calendar Year Totals?

 

[adiles]

Yes, in 2013 the no of days is 350, in 2014 365 and in 2015 its just 14 days. I want to calculate the no of days with Per day

 

[gaz_chops]

Try

=IF($B11<EDATE(G$10,12)-1,MAX(0,MIN($C11+1,EDATE(G$10,12)-1+1)-MAX($B11,G$10)))*$F11

 

[adiles]

Try

=IF($B11<EDATE(G$10,12)-1,MAX(0,MIN($C11+1,EDATE(G$10,12)-1+1)-MAX($B11,G$10)))*$F11

View attachment 16754

The result is zero for me.

 

[gaz_chops]

Did you change the cell references to match your data? My data starts in Row 10!

=IF($B11<EDATE(G$10,12)-1,MAX(0,MIN($C11+1,EDATE(G$10,12)-1+1)-MAX($B11,G$10)))*$F11

 

[adiles]

Did you change the cell references to match your data? My data starts in Row 10!

=IF($B11<EDATE(G$10,12)-1,MAX(0,MIN($C11+1,EDATE(G$10,12)-1+1)-MAX($B11,G$10)))*$F11

Yes, I changed it according to my data, the closing brackets are like this only right?

 

[adiles]

=IF($B11<EDATE(P8,12)-1,MAX(0,MIN($C11+1,EDATE(P8,12)-1+1)-MAX($B11,P8)))*N11

This is my cell ref.

 

[gaz_chops]

Yes