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]

Welcome to the forum, try

=IF(AND($B2<EOMONTH(G$1,0),$C2>G$1),MIN((MAX($C2,G$1)+1-G$1),(EOMONTH(G$1,0)+1-MAX($B2,G$1)))*$F2,)

 

[gaz_chops]

Adjusted for month 1

=IF(AND($B2<EOMONTH(G$1,0),$C2>G$1),MIN((MAX($C2,G$1)+IF($B2>G$1,0,1)-G$1),(EOMONTH(G$1,0)+IF($B2>G$1,0,1)-MAX($B2,G$1)))*$F2,)

 

[adiles]

@gaz_chops Thanks alot. You are a real life saver. Thanks alot

 

[gaz_chops]

You're welcome.

 

[adiles]

You're welcome.

You are really awesome !

 

[Dave Patton]

An alternative that you can try. This formula gives a different amount for the first month and the 12 months sum to the specified amount.

The second suggestion just eliminates the need for the days and daily amount columns.

T202006a.xlsm
	B	C	D	E	F	G	H	I
1	Start	End	Amount	Days	Daily Amt	Jan-2013	Feb-2013	Mar-2013
2	15-Jan-13	14-Jan-14	$80,000.00	365	219.18	3,726.03	6,136.99	6,794.52
3	01-Jan-13	31-May-13	$1,000.00	151	6.62	205.30	185.43	205.30
4
5
6	Start	End	Amount	Jan-2013	Feb-2013	Mar-2013	Apr-2013	May-2013
7	15-Jan-13	14-Jan-14	$80,000.00	3,726.03	6,136.99	6,794.52	6,575.34	6,794.52
8	01-Jan-13	31-May-13	$1,000.00	205.30	185.43	205.30	198.68	205.30
9
9a

Cell Formulas
Range	Formula
E2:E3	E2	=C2-B2+1
F2:F3	F2	=D2/(C2-B2+1)
G2:I3	G2	=MAX(0, MIN(EOMONTH(G$1, 0), $C2) + 1 - MAX(G$1, $B2))*$F2
E7:I8	E7	=MAX(0, MIN(EOMONTH(G$1, 0), $C7) + 1 - MAX(G$1, $B7))*$D7/($C7-$B7+1)

 

[gaz_chops]

Ignore this post

 

[Dave Patton]

correction to E8

T202006a.xlsm
	B	C	D	E	F	G	H	I	J	K
1	Start	End	Amount	Days	Daily Amt	Jan-2013	Feb-2013	Mar-2013	Apr-2013	May-2013
2	15-Jan-13	14-Jan-14	$80,000.00	365	219.18	3,726.03	6,136.99	6,794.52	6,575.34	6,794.52
3	01-Jan-13	31-Dec-13	$365.00	365	1.00	31.00	28.00	31.00	30.00	31.00
4	01-Jan-13	31-May-13	$1,000.00	151	6.62	205.30	185.43	205.30	198.68	205.30
5
6
7	Start	End	Amount	Jan-2013	Feb-2013	Mar-2013	Apr-2013	May-2013	Jun-2013	Jul-2013
8	15-Jan-13	14-Jan-14	$80,000.00	3,726.03	6,136.99	6,794.52	6,575.34	6,794.52	6,575.34	6,794.52
9	01-Jan-13	31-Dec-13	$365.00	31.00	28.00	31.00	30.00	31.00	30.00	31.00
10
9a

Cell Formulas
Range	Formula
E2:E4	E2	=C2-B2+1
F2:F4	F2	=D2/(C2-B2+1)
G2:K4	G2	=MAX(0, MIN(EOMONTH(G$1, 0), $C2) + 1 - MAX(G$1, $B2))*$F2
E8:K9	E8	=MAX(0, MIN(EOMONTH(E$7, 0), $C8) + 1 - MAX(E$7, $B8))*$D8/($C8-$B8+1)

 

[adiles]

@gaz_chops Hi, can you please help me out with yearly also. I want to display the amounts at year end. What will be the formula ?