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.

 

[fisch3341]

fisch3341
Welcome to the forum.
You could have started a thread with your question?
You did not describe your information.
You can copy the data below to a clean sheet; see the icon below the F(x).
H2:X2 are dates; month end dates like Oct 31, 2020.

T202011a.xlsm
	C	D	E	F	G	H	I	J	K	L
1
2	Amount	Start date	End Date	Number of Months		Oct	Nov	Dec	Jan	Feb
3	156,643.84	01-Nov-20	28-Feb-21	3			52,214.61	52,214.61	52,214.61
4
5b

Cell Formulas
Range	Formula
F3	F3	=DATEDIF(D3-1,E3,"M")
H3:L3	H3	=IF(AND($D3<=H$2,SUM($G3:G3)<$C3),$C3/$F3,"")

Hi Dave,

I tried this, and it worked for the 3 months. Now what would I have to do to put that formula in for the other months prior?
They should be pulling 0 since it doesn't start till November, but for some reason it's pulling that amount for other months as well:

 

[Dave Patton]

You have not provided information that I can review or comment on.
Ensure you copied the data that I posted to C1.
Column G should be blank.
You can post an extract of your data with the forums Add-in XL2BB.

 

[Dave Patton]

T202011a.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	Amount	Start date	End Date	Number of Months		Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21
3	156,643.84	01-Nov-20	28-Feb-21	4												39,160.96	39,160.96	39,160.96	39,160.96
4	24,000.00	01-Jan-20	31-Dec-20	12		2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00
5b

Cell Formulas
Range	Formula
H4:T4,H3:U3	H3	=IF(AND($D3<=H$2,SUM($G3:G3)<$C3),$C3/$F3,"")
F3:F4	F3	=DATEDIF(D3,E3+1,"M")

 

[Dave Patton]

N.B. Nov 1 to Feb 28 is 4 months.

Review the logic.
You can use Excel's Evaluate Formula (Formulas Formula Auditing Evaluate Formula)

 

[haeescobar]

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,)

View attachment 16121

Hi, could you please explain what am I doing wrong? I'm using the same formula but the result is just the amount per day. Thanks in advance!!!