Need Formula for Monthly Calculation

[NonStopLeo]

Dear Friends

I need a formula to amortize my expenses in below sheet. I want in Column L monthly amortization on the basis, if full month is used then it should calculate for 30 days, if not full month then calculate based on day used in that month.
Days in year base should be 30 days. Kindly help.

COMPANY NAME
INSURANCE AMORTIZATION				Opening			Closing
							01-Feb-18			28-Feb-18
						Cost	Opening	Closing
S.No	Start Date	End Date	Ref No	Description	No of Days	AMOUNT	No of Days	Amortization	No of Days	Amortization	Charge for the Month	Prepaid Balance
1
2	1-Oct-17	31-Dec-17	BPV-8417	ADCO Fujairah	90.00	3,600.00	90.00	3,600.00	90.00	3,600.00	-	-
3	11-Oct-17	7-Feb-18	BPV-8489	Mirfa Land - Bouroge	116.00	5,530.00	110.00	5,243.97	116.00	5,530.00	286.03	-
4	6-Feb-17	5-Feb-18	6677	01/4102/41/2017/50	359.00	2,712.00	355.00	2,681.78	359.00	2,712.00	30.22	-
5	1-Mar-17	28-Feb-18	15763	01/4111/41/2017/14	357.00	2,220.00	330.00	2,052.10	357.00	2,220.00	167.90	-
6	15-Mar-17	28-Feb-18	14988	01/4111/41/2017/14	343.00	3,232.00	316.00	2,977.59	343.00	3,232.00	254.41	-
7	1-Apr-17	31-Mar-18	W/C	01/1151/11/2016/38	360.00	4,588.00	300.00	3,823.33	330.00	4,205.67	382.33	382.33
8	11-Jul-17	28-Feb-18	78427	01/4111/41/2017/14	227.00	1,681.13	200.00	1,481.17	227.00	1,681.13	199.96	-
9	20-Jul-17	15-Jan-18	99609	01/4102/41/2017/251	175.00	1,127.00	175.00	1,127.00	175.00	1,127.00	-	-
10	28-Jul-17	25-Jan-18	56570	01/4102/41/2017/259	177.00	3,226.00	177.00	3,226.00	177.00	3,226.00	-	-
11	7-Aug-17	30-Jul-18	70691	01/4102/41/2017/263	353.00	2,182.00	174.00	1,075.55	204.00	1,260.99	185.44	921.01
12	17-Aug-17	16-Aug-18	93187	01/4102/41/2017/268	359.00	2,500.00	164.00	1,142.06	194.00	1,350.97	208.91	1,149.03
13	27-Aug-17	26-Aug-18	87816	01/4102/41/2017/287	359.00	6,050.00	154.00	2,595.26	184.00	3,100.84	505.57	2,949.16
14	25-Sep-17	28-Feb-18	85151	01/4111/41/2017/14	153.00	2,293.00	126.00	1,888.35	153.00	2,293.00	404.65	-
15	23-Aug-17	15-Feb-18	68521	01/4102/41/2017/274	172.00	1,300.00	158.00	1,194.19	172.00	1,300.00	105.81	-
16	10-Sep-17	9-Sep-18	16999	01/4102/41/2017/303	359.00	2,345.00	141.00	921.02	171.00	1,116.98	195.96	1,228.02
17	18-Sep-17	28-Feb-18	99717	01/4111/41/2017/14	160.00	1,065.00	133.00	885.28	160.00	1,065.00	179.72	-
18	30-Oct-17	30-Apr-18	54234	05/4102/41/2017/191	180.00	2,470.00	91.00	1,248.72	121.00	1,660.39	411.67	809.61
19	26-Oct-17	20-Apr-18	68137	05/4102/41/2017/189	174.00	2,308.00	95.00	1,260.11	125.00	1,658.05	397.93	649.95
20	26-Oct-17	15-Oct-18	98959	05/4102/41/2017/175	349.00	1,728.00	95.00	470.37	125.00	618.91	148.54	1,109.09
21	2-Nov-17	30-Apr-18	75941	05/4102/41/2017/193	178.00	1,922.00	89.00	961.00	118.00	1,274.13	313.13	647.87
22	10-Nov-17	5-May-18	53892	05/4102/41/2017/197	175.00	2,416.00	81.00	1,118.26	110.00	1,518.63	400.37	897.37
23	9-Nov-17	8-Dec-18	96115	05/4102/41/2017/198	389.00	1,790.00	82.00	377.33	111.00	510.77	133.44	1,279.23
24	29-Nov-17	28-Feb-18	67183	01/4111/41/2017/14	89.00	559.00	62.00	389.42	89.00	559.00	169.58	-
25	29-Nov-17	28-Feb-18	16574	01/4111/41/2017/14	89.00	560.00	62.00	390.11	89.00	560.00	169.89	-
26	6-Dec-17	28-Feb-18	97201	01/4111/41/2017/14	82.00	517.00	55.00	346.77	82.00	517.00	170.23	-
27	27-Nov-17	28-Feb-18	64160	01/4111/41/2017/14	91.00	1,366.00	64.00	960.70	91.00	1,366.00	405.30	-
28	11-Dec-17	28-Feb-18	26890	01/4111/41/2017/14	77.00	511.00	50.00	331.82	77.00	511.00	179.18	-
29	13-Dec-17	12-Dec-18	85170	05/4102/41/2017/218	359.00	3,202.00	48.00	428.12	77.00	686.78	258.66	2,515.22
30	28-Dec-17	27-Dec-18	65705	05/4102/41/2017/237	359.00	4,410.00	33.00	405.38	62.00	761.62	356.24	3,648.38
31	21-Dec-17	28-Feb-18	74061	01/4111/41/2017/14	67.00	292.00	40.00	174.33	67.00	292.00	117.67	-
				TOTAL		69,702.13		44,777.09		51,515.85	6,738.75	18,186.28

<colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[StephenCrump]

I can replicate almost all these numbers using the formulae:

F9: =DAYS360(B9,C9)
H9: =DAYS360(B9,MIN(C9,H$4))
J9: =DAYS360(B9,MIN(C9,1+K$4))
L9: =ROUND((J9-H9)/F9*G9,2)

However, there are five rows with a one day discrepancy. For example, for S.No = 21, cell J2 shows 118 days, whereas I have 119. This may be an error. Or it may be due to the particular version of the 360 day calendar you're using. Excel uses US/NASD. Do you know what you're using?

 

[NonStopLeo]

I can replicate almost all these numbers using the formulae:

F9: =DAYS360(B9,C9)
H9: =DAYS360(B9,MIN(C9,H$4))
J9: =DAYS360(B9,MIN(C9,1+K$4))
L9: =ROUND((J9-H9)/F9*G9,2)

However, there are five rows with a one day discrepancy. For example, for S.No = 21, cell J2 shows 118 days, whereas I have 119. This may be an error. Or it may be due to the particular version of the 360 day calendar you're using. Excel uses US/NASD. Do you know what you're using?

Thanks for your Reply, However it is not giving the desired results.

Column H & J shouldn’t be Negative
Column F & J should be 360 if Start & End Dates are Full Year, Currently it is giving different, i.e 357,359,360
Charge For the Month should be Equal to 30 Days if Full Month is Used, Currently it is coming 27 in Case of February i.e S.No 6 & 8
I am Using US NASD Method i.e FALSE

COMPANY NAME
INSURANCE AMORTIZATION				Opening			Closing
							01-Feb-18			28-Feb-18
						Cost	Opening	Closing
S.No	Start Date	End Date	Ref No	Description	No of Days	AMOUNT	No of Days	Amortization	No of Days	Amortization	Charge for the Month	Prepaid Balance	REMARKS
1
2	1-Oct-17	31-Dec-17	BPV-8417	ADCO Fujairah	90.00	3,600.00	90.00	3,600.00	90.00	3,600.00	-	-
3	11-Oct-17	7-Feb-18	BPV-8489	Mirfa Land - Bouroge	116.00	5,530.00	110.00	5,243.97	116.00	5,530.00	286.03	-
4	6-Feb-17	5-Feb-18	6677	01/4102/41/2017/50	359.00	2,712.00	355.00	2,681.78	359.00	2,712.00	30.22	-	Column F & J should be 360
5	1-Mar-17	28-Feb-18	15763	01/4111/41/2017/14	357.00	2,220.00	330.00	2,052.10	357.00	2,220.00	167.90	-	Column F & J should be 360
6	15-Mar-17	28-Feb-18	14988	01/4111/41/2017/14	343.00	3,232.00	316.00	2,977.59	343.00	3,232.00	254.41	-
7	1-Apr-17	31-Mar-18	W/C	01/1151/11/2016/38	360.00	4,588.00	300.00	3,823.33	330.00	4,205.67	382.33	382.33
8	1-Mar-17	28-Feb-18	78427	01/4111/41/2017/14	357.00	1,681.13	330.00	1,553.99	357.00	1,681.13	127.14	-	Column F & J should be 360
9	20-Jul-17	15-Jan-18	99609	01/4102/41/2017/251	175.00	1,127.00	175.00	1,127.00	175.00	1,127.00	-	-
10	28-Jul-17	25-Jan-18	56570	01/4102/41/2017/259	177.00	3,226.00	177.00	3,226.00	177.00	3,226.00	-	-
11	7-Aug-17	30-Jul-18	70691	01/4102/41/2017/263	353.00	2,182.00	174.00	1,075.55	204.00	1,260.99	185.44	921.01
12	17-Aug-17	16-Aug-18	93187	01/4102/41/2017/268	359.00	2,500.00	164.00	1,142.06	194.00	1,350.97	208.91	1,149.03	Column F should be 360
13	27-Aug-17	26-Aug-18	87816	01/4102/41/2017/287	359.00	6,050.00	154.00	2,595.26	184.00	3,100.84	505.57	2,949.16	Column F should be 360
14	25-Sep-17	28-Feb-18	85151	01/4111/41/2017/14	153.00	2,293.00	126.00	1,888.35	153.00	2,293.00	404.65	-
15	23-Aug-17	15-Feb-18	68521	01/4102/41/2017/274	172.00	1,300.00	158.00	1,194.19	172.00	1,300.00	105.81	-
16	10-Sep-17	9-Sep-18	16999	01/4102/41/2017/303	359.00	2,345.00	141.00	921.02	171.00	1,116.98	195.96	1,228.02	Column F should be 360
17	18-Sep-17	28-Feb-18	99717	01/4111/41/2017/14	160.00	1,065.00	133.00	885.28	160.00	1,065.00	179.72	-
18	30-Oct-17	30-Apr-18	54234	05/4102/41/2017/191	180.00	2,470.00	91.00	1,248.72	121.00	1,660.39	411.67	809.61
19	26-Oct-17	20-Apr-18	68137	05/4102/41/2017/189	174.00	2,308.00	95.00	1,260.11	125.00	1,658.05	397.93	649.95
20	26-Oct-17	15-Oct-18	98959	05/4102/41/2017/175	349.00	1,728.00	95.00	470.37	125.00	618.91	148.54	1,109.09
21	2-Nov-17	30-Apr-18	75941	05/4102/41/2017/193	178.00	1,922.00	89.00	961.00	119.00	1,284.93	323.93	637.07
22	10-Nov-17	5-May-18	53892	05/4102/41/2017/197	175.00	2,416.00	81.00	1,118.26	111.00	1,532.43	414.17	883.57
23	9-Nov-17	8-Nov-18	96115	05/4102/41/2017/198	359.00	1,790.00	82.00	408.86	112.00	558.44	149.58	1,231.56	Column F should be 360
24	29-Nov-17	28-Feb-18	67183	01/4111/41/2017/14	89.00	559.00	62.00	389.42	89.00	559.00	169.58	-
25	29-Nov-17	28-Feb-18	16574	01/4111/41/2017/14	89.00	560.00	62.00	390.11	89.00	560.00	169.89	-
26	6-Dec-17	28-Feb-18	97201	01/4111/41/2017/14	82.00	517.00	55.00	346.77	82.00	517.00	170.23	-
27	27-Nov-17	28-Feb-18	64160	01/4111/41/2017/14	91.00	1,366.00	64.00	960.70	91.00	1,366.00	405.30	-
28	11-Dec-17	28-Feb-18	26890	01/4111/41/2017/14	77.00	511.00	50.00	331.82	77.00	511.00	179.18	-
29	13-Dec-17	12-Dec-18	85170	05/4102/41/2017/218	359.00	3,202.00	48.00	428.12	78.00	695.70	267.58	2,506.30	Column F should be 360
30	28-Dec-17	27-Dec-18	65705	05/4102/41/2017/237	359.00	4,410.00	33.00	405.38	63.00	773.90	368.52	3,636.10	Column F should be 360
31	21-Dec-17	28-Feb-18	74061	01/4111/41/2017/14	67.00	292.00	40.00	174.33	67.00	292.00	117.67	-	Column H & J shouldn’t be Negative
32	1-Jan-18	31-Dec-18	74062	01/4111/41/2017/15	360.00	2,000.00	30.00	166.67	60.00	333.33	166.67	1,666.67	Column H & J shouldn’t be Negative
				TOTAL		71,702.13		45,048.11		51,942.66	6,894.55	19,759.47

<colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[StephenCrump]

I have given you some formulae which replicate most of your numbers, which I took as being correct. But now you're saying that some of the numbers you've given us aren't correct?

Based on your latest post, I am guessing that F9 should be =DAYS360(B19,1+C19). This gives 360 days for all these possibilities:

Book1
	B	C	D	E	F
9	1 Apr 2017	31 Mar 2018			360
10	1 Mar 2017	28 Feb 2018			360
11	15 Feb 2017	14 Feb 2018			360
12	1 Mar 2015	29 Feb 2016			360
13	12 Mar 2018	11 Mar 2019			360
14	17-Aug-17	16-Aug-18			360
15	27-Aug-17	26-Aug-18			360
Sheet1

But this would imply that most of the numbers you have posted are incorrect. Where do these numbers come from?

 

[NonStopLeo]

I have given you some formulae which replicate most of your numbers, which I took as being correct. But now you're saying that some of the numbers you've given us aren't correct?

Based on your latest post, I am guessing that F9 should be =DAYS360(B19,1+C19). This gives 360 days for all these possibilities:

	B	C	D	E	F
9	1 Apr 2017	31 Mar 2018			360
10	1 Mar 2017	28 Feb 2018			360
11	15 Feb 2017	14 Feb 2018			360
12	1 Mar 2015	29 Feb 2016			360
13	12 Mar 2018	11 Mar 2019			360
14	17-Aug-17	16-Aug-18			360
15	27-Aug-17	26-Aug-18			360

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

But this would imply that most of the numbers you have posted are incorrect. Where do these numbers come from?

Can you please guide me how I can upload my excel sheet with you for your review. thanks

 

[joeu2004]

Can you please guide me how I can upload my excel sheet with you for your review

I would very much like to see this Excel file, too.

I suggest that you upload the file (redacted) to a file-sharing website (e.g. box.net/files) and post the public/share URL in a response here. Test the download URL first, being careful to log out of the file-sharing website. (If you use box.net/files, ignore any preview errors; just do the download.)

Some participants object to doing that because they cannot or will not be able to download the file. Too bad. An alternative is for you to post every (representative) formula, which is tedious. But sometimes, the devil is in details that we cannot see from posted formulas and values alone.

 

[NonStopLeo]

I have given you some formulae which replicate most of your numbers, which I took as being correct. But now you're saying that some of the numbers you've given us aren't correct?

Based on your latest post, I am guessing that F9 should be =DAYS360(B19,1+C19). This gives 360 days for all these possibilities:

	B	C	D	E	F
9	1 Apr 2017	31 Mar 2018			360
10	1 Mar 2017	28 Feb 2018			360
11	15 Feb 2017	14 Feb 2018			360
12	1 Mar 2015	29 Feb 2016			360
13	12 Mar 2018	11 Mar 2019			360
14	17-Aug-17	16-Aug-18			360
15	27-Aug-17	26-Aug-18			360

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

But this would imply that most of the numbers you have posted are incorrect. Where do these numbers come from?

It is solved Sir. Just by a little modifications and adding 1+ in formula, it is giving the desired results now. Many Thanks for your help

 

[StephenCrump]

It is solved Sir. Just by a little modifications and adding 1+ in formula, it is giving the desired results now.

Great, I'm glad you worked it out.

There are at least two people here curious to see what formulae you used in the end. Can you please post either a screenshot and the formulae used, or attach a workbook as joeu2004 explained in Post #6 ?

 

[NonStopLeo]

I would very much like to see this Excel file, too.

I suggest that you upload the file (redacted) to a file-sharing website (e.g. box.net/files) and post the public/share URL in a response here. Test the download URL first, being careful to log out of the file-sharing website. (If you use box.net/files, ignore any preview errors; just do the download.)

Some participants object to doing that because they cannot or will not be able to download the file. Too bad. An alternative is for you to post every (representative) formula, which is tedious. But sometimes, the devil is in details that we cannot see from posted formulas and values alone.

Great, I'm glad you worked it out.

There are at least two people here curious to see what formulae you used in the end. Can you please post either a screenshot and the formulae used, or attach a workbook as joeu2004 explained in Post #6 ?

Dear Sirs

Here is my file
https://jumpshare.com/v/w0auT98281kUSe14O5fQ
Thanks again for your kind help.

 

[StephenCrump]

Thanks for posting your solution. It all looks reasonable, although you could simplify some of your formulae:

I9: =IF((DAYS360(E9,MIN(1+F9,I$4)))<0,0,(DAYS360(E9,MIN(1+F9,I$4))))
--> =MAX(0,DAYS360(E9,MIN(1+F9,I$4)))

J9:=IF(I9<=G9,(((H9)/G9)*I9),(((H9)/G9)*G9))
--> =H9*I9/G9
(The formula used in I9 means that I9 can never exceed G9)

And similar comments apply for K9 and L9.

You may also want to round your amortisations to the cent, so that you don't get rounding discrepancies.