how to calculate amortization amount

[bigdan]

Hi guys, I'm having trouble figuring out the formula for the amortization amount I should choose.

In the table below I've entered in the values that should be in there. I've tested it in 4 scenarios. In the first 2 it works fine, in the second two it doesnt. I'm hoping someone can advise.

The main thing I need help with is col K. I'm assuming the current month is June, so I need to calculate the amortized amount for June. The way to do that is to divide the total dollar amount by the number of months, which gives the monthly amount; however since there cant be anything before the current month (Jan-May), I'd want June to have the YTD figures. Even that would be easy enough if everything was starting in Jan, but often times something starts in April or June itself.

That all sounds pretty complicated but the table below has the correct values, so I'm hoping someone can advise.

Thanks so much!

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Accounted Net	Coverage Period	Start	End	# of Months	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Amortized
2	1,000.00	Jan - Dec 2016	Jan	Dec	12						500	83	83	83	83	83	83	1,000
3	1,000.00	Jan - Aug 2016	Jan	Aug	8						750	125	125					1,000
4	1,000.00	Apr - June 2016	Apr	Jun	3						1,000							1,000
5	1,000.00	Apr - July 2016	Apr	Jul	4						750	250						1,000
6
7
8	Current Month	6
Sheet2

Cell Formulas
Range	Formula
L3	=$A3/$E3
L5	=A5/E5
L2	=$A2/$E2
M3	=$A3/$E3
M2	=$A2/$E2
K5	=A5/E5*3
K2	=$A2/$E2*$B$8
N2	=$A2/$E2
O2	=$A2/$E2
P2	=$A2/$E2
Q2	=$A2/$E2
R2	=SUM(F2:Q2)

 

[gurs]

If I understand what you are looking for, a solution is attached and below. I changed the spreadsheet slightly so that the Start, End, Current Month and monthly column headings contain actual dates. Once that is done, the formula for cell F2 is:

=IF(F$1<$B$8,0,IF(F$1<$C2,0,IF(F$1>$D2,0,$A2/$E2+IF(F$1=$B$8,((YEAR($B$8)-YEAR($C2))*12+MONTH($B$8)-MONTH($C2))*($A2/$E2),0))))

This formula can be filled down/right for all cells in F2:Q5. The formula basically checks to see if the current month is within the range of the start/end dates, and if so whether there is accumulated billing that needs to be included from policy months prior to the Current Month. See the "Solution" tab of this spreadsheet for the implemented solution.

Hope that's helpful.

 

[bigdan]

If I understand what you are looking for, a solution is attached and below. I changed the spreadsheet slightly so that the Start, End, Current Month and monthly column headings contain actual dates. Once that is done, the formula for cell F2 is:

=IF(F$1<$B$8,0,IF(F$1<$C2,0,IF(F$1>$D2,0,$A2/$E2+IF(F$1=$B$8,((YEAR($B$8)-YEAR($C2))*12+MONTH($B$8)-MONTH($C2))*($A2/$E2),0))))

This formula can be filled down/right for all cells in F2:Q5. The formula basically checks to see if the current month is within the range of the start/end dates, and if so whether there is accumulated billing that needs to be included from policy months prior to the Current Month. See the "Solution" tab of this spreadsheet for the implemented solution.

Hope that's helpful.

Thanks so much! For the solution, as well as showing me a new way to do spreadsheets for example purposes. Looks awesome!

 

[gurs]

Glad it did the trick!

 

[gurs]

See the "Solution" tab of this spreadsheet for the implemented solution.

Updated link for solution: https://1drv.ms/x/s!ABnwOn7Lh6gYiP9C

 

[bigdan]

For future reference, is there a way to see the formulas in this worksheet without downloading? I thought I was able to see them when you first uploaded this but I'm not seeing it now somehow.

 

[gurs]

For future reference, is there a way to see the formulas in this worksheet without downloading? I thought I was able to see them when you first uploaded this but I'm not seeing it now somehow.

I gave you the formula for cell F2 in my first post. You just have to make sure you are using actual dates for Start, End, Current Month and monthly column headings. Once you do that, you can fill my F2 formula down/right. Or just download the sample workbook.