Prepaid Expense amortization formula

sunshinegal · Feb 28, 2022

Hi,

I am trying to create a dynamic prepaid expense amortization template with a formula that can be used for any new prepaid expenses. I need it to look at the start date and the date paid, and calculate based on whichever date is the later date, and be able to calculated the first month and last month on a prorated basis as the dates may start in the middle of the month.

Can anyone help?

Thanks in advance!

Name	Date paid	Start date	End date	No. of days	Amount	Per day	# of days (from payment date to end date)	Balance to set up as prepaid expense	Apr-21	May-21	Jun-21	Jul-21	Aug-21
Software license	12-Jan-22	01-Nov-21	31-Oct-22	365	100,000.00	273.97	293	80,273.97	-	-	-	-	-
Software license	05-Nov-21	01-Apr-21	31-Mar-23	730	200,000.00	273.97	511	140,000.00

BSALV · Feb 28, 2022

as starter, but i need more explanaition

Map2

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

2

Name

Date paid

Start date

End date

No, of days

Amount

Per day

# of days (from payment date to end date)

Balance to set up as prepaid expense

01-03-21

3

Software license

1/11/2021

31/10/2022

365

100000

274 €

293

80.274 €

4

Software license

1/04/2021

31/03/2022

365

200000

548 €

511 €

140.000 €

Blad1

Cell Formulas
Range	Formula
K2:AD2	K2	=EDATE(J2,1)
J3:AD4	J3	=IF(J$2>=$C3,$F3-$G3*MAX(0,MIN(J$2,$D3)+1-$C3),"-")
E3:E4	E3	=+D3-C3+1
G3:G4	G3	=(+F3/E3)*1

sunshinegal · Feb 28, 2022

Thanks so much BSALV! What did you need explanation for? The start date and end dates are the periods of service being rendered, and the paid date is the date the invoice was actually paid. I was sort of hoping to be able to keep both dates in the schedule.