add and multiply values based on date reference

[fragelracken]

Ultimately I'm trying to calculate Interest accrued from a specific date. The problem is payments can be interest only which when used should keep the next months interest at the same rate instead of decreasing because the principal hasn't been paid down.

Im having problems when over the course of a number of payments they are principal + Interest and Interest only which requires at times to add up the same interest amount for 2-3 months in a row, then add up a different interest rate, etc. Additionally if the reference date is say on the 15th and the interest date is the 1st then for the last month I need to calculate the interest on a daily basis based on the current interest rate for that month.

The reference cell is H5, the Date ranges are J2 - J12, and the principal fields are: K2 - K12.

In the next post I will include the Amortization schedule.

In this example the interest is accrued at the same rate months 1-7, then decreased for months 8 and 9. so the total interest accrued should be eqaual to: $7487.74 (see next post for schedule).

ZZ_Loan.Calculator-Update.1.1.xls
	F	G	H	I	J	K	L	M
1			v 1.0		PaymentApply Date	Principal + Interest	Additional Principal	Interest Only
2					1/1/2009			833.33
3					2/1/2009			833.33
4	Quarter Close Backward				3/1/2009			833.33
5		Close Date	9/1/2009		4/1/2009			833.33
6		Days of Interest	0		5/1/2009			833.33
7		Beg Principal Balance	$100,000.00		6/1/2009			833.33
8		Paydowns	$980.41		7/1/2009	1,321.51
9		End Principal Balance	$99,019.59		8/1/2009	1,321.51
10		Net Principle Due	$3,562.50		9/1/2009			829.27
11		Interest Due & Accrued			10/1/2009	1,321.51
12		Interest Earned (cummulative)	$7,350.66		11/1/2009	1,321.51
13		Interest pymts (cummulative)	$7,350.66
14		Net Interest Receivable	$0.00
TEST

 

[fragelracken]

Amortization schedule

Amortization schedule.

In this example the interest is accrued at the same rate months 1-7, then decreased for months 8 and 9. so the total interest accrued should be eqaual to: $7487.74 (see next post for schedule).

=E37+E37+E37+E37+E37+E37+E37+E38+E39

ZZ_Loan.Calculator-Update.1.1.xls
	A	B	C	D	E	F	G	H
35	No.	Due Date	Payment Due	Additional Principal	Interest	Principal	Balance
36							$ 100,000.00
37	1	1/1/2009	1,321.51	0.0000	833.33	488.18	99,511.82
38	2	2/1/2009	1,321.51	0.0000	829.27	492.24	99,019.59
39	3	3/1/2009	1,321.51	0.00	825.16	496.35	98,523.24
40	4	4/1/2009	1,321.51	0.00	821.03	500.48	98,022.76
41	5	5/1/2009	1,321.51	0.00	816.86	504.65	97,518.11
42	6	6/1/2009	1,321.51	0.00	812.65	508.86	97,009.26
43	7	7/1/2009	1,321.51	0.00	808.41	513.10	96,496.16
44	8	8/1/2009	1,321.51	0.00	804.13	517.38	95,978.78
45	9	9/1/2009	1,321.51	0.00	799.82	521.69	95,457.09
46	10	10/1/2009	1,321.51	0.00	795.48	526.03	94,931.07
47	11	11/1/2009	1,321.51	0.00	791.09	530.42	94,400.65
48	12	12/1/2009	1,321.51	0.00	786.67	534.84	93,865.81
49	13	1/1/2010	1,321.51	0.00	782.22	539.29	93,326.52
TEST

Thank you for any help you can offer.

 

[AnalyticsGuy7]

hmmm... the specifics of this make it rather complicated.


Is interest ever to increase if it isn't paid? Does the interest level decrease more than one level if additional principal is added?

 

[fragelracken]

Q. Is interest ever to increase if it isn't paid?
A. The loan is a fixed rate with fixed number of payments (not vairable). Interest Rate does not increase and will not decrease in amount unless principal is paid down. If there is an interest only payment, the current payment will be required to be paid again which could be a full payment of 1321.51, or interest only at 833.33 at the same rate since principal wasn't paid down.

All payments have the same fixed amount 1,321.51. the 1st payment is amoritized by 833.33 in Interest and 488.18 in principal. If there is an interest only payment (no principal), then the second payment will be identical to the 1st payment until principal is paid down. If they make 2 Interest only payments, their 3rd payment will still remain the same amount as the 1st @ 833.33 in Interest and 488.18 in principal. When the principal is finally paid down it wll decrease the next payment interest based on the amoritization schedule.

Q. Does the interest level decrease more than one level if additional principal is added?
A. My amoritization schedule automatically adjusts if there is an additional principal paid above the standard amount owed in column L (Anything entered in column L will subtract principal in column D of the amoritizaiton schedule and auto adjust the remaining payments accordingly. The number of payments and payment amount will not change, but the amount towards interest and principal will adjust automatically. If there is a way to reference cells J - M to calculate amounts in cells E as defined by the close date, there is no need to worry about decreasing interest.

My issue is in automating the ability to calculate the exact amount of interest accrued.

I posted a question similar in nature, but requesting a different type of outout. the syntax below seems to have the logic I need without the results I'm looking for. Here is the syntax:

=SUMPRODUCT(--(J2:J361<=H5),--(K2:K361&M2:M361<>""))

In this example it is doing a count of how many times a colum has data entered in it if another cell has a date less than a specific value and the cells are not empty.

http://www.mrexcel.com/forum/showthread.php?t=385986

thank you for your help and please let me know if I need to provide more informaiton.

 

[AnalyticsGuy7]

I'm not sure if this will help you, but here's what I've done...

On the first worksheet that you have posted, I inserted two columns N and O.

For Column N, I have the following formula in N2 (since you always pay the first interest on the first month?):

=INDEX(Lookup!$E$37:$E$49, 1, 0)

Then on N3 (and copied all the way down column N): I have called the second sheet you posted "Lookup".

=IF(K2="",N2,INDEX(Lookup!$E$37:$E$13,MATCH(Sheet1!N2,Lookup!$E$37:$E$49,0)+1))

This formula above says that if you didn't make a Principal + Interest Payment, then your interest for that month is the same as above. However, if you DID make a Principal + Interest that month, then your interest is one level decreased (on the lookup sheet).


Column O simply has a running sum of these interest amounts:

(In O2 copied all of the way down):

=SUM(N$2:N2)


Finally, for a way to look up a date, pull in the total amount of interest + the interest for that month/30 * extra days for that month:


In my sheet I do not have your columns A through H, so you'll have to adjust these formulas based on where you want to put them...

In my E5 I have the date you enter.
In my E6, I lookup the running Interest total (from column O) based on that date:

=VLOOKUP(E5,J2:O19,6)

In my E7 I count the number of extra days (not sure if it would be minus one or not...):

=DAY(E5)-1

Then in E8 In lookup the interest rate at that date, divide by 30, and multiply by that number of extra days:

=(VLOOKUP(E5,J2:N19,5)/30)*E7

Finally, I add up that extra amount to the original amount:

=E8+E6


I know this is super confusing and may need to be adjusted based on the assumptions, but maybe it is at least a little bit for you to work with?

 

[AnalyticsGuy7]

See the edit in red...

I'm not sure if this will help you, but here's what I've done...

On the first worksheet that you have posted, I inserted two columns N and O.

For Column N, I have the following formula in N2 (since you always pay the first interest on the first month?):

=INDEX(Lookup!$E$37:$E$49, 1, 0)

Then on N3 (and copied all the way down column N): I have called the second sheet you posted "Lookup".

=IF(K2="",N2,INDEX(Lookup!$E$37:$E$49,MATCH(Sheet1!N2,Lookup!$E$37:$E$49,0)+1))

This formula above says that if you didn't make a Principal + Interest Payment, then your interest for that month is the same as above. However, if you DID make a Principal + Interest that month, then your interest is one level decreased (on the lookup sheet).


Column O simply has a running sum of these interest amounts:

(In O2 copied all of the way down):

=SUM(N$2:N2)


Finally, for a way to look up a date, pull in the total amount of interest + the interest for that month/30 * extra days for that month:


In my sheet I do not have your columns A through H, so you'll have to adjust these formulas based on where you want to put them...

In my E5 I have the date you enter.
In my E6, I lookup the running Interest total (from column O) based on that date:

=VLOOKUP(E5,J2:O19,6)

In my E7 I count the number of extra days (not sure if it would be minus one or not...):

=DAY(E5)-1

Then in E8 In lookup the interest rate at that date, divide by 30, and multiply by that number of extra days:

=(VLOOKUP(E5,J2:N19,5)/30)*E7

Finally, I add up that extra amount to the original amount:

=E8+E6


I know this is super confusing and may need to be adjusted based on the assumptions, but maybe it is at least a little bit for you to work with?

 

[fragelracken]

Thank you AnalyticsGuy7

It is very helpful. I have gotten the 1st 1/2 of your recommendation completed, but I am a little confused on the second 1/2; perhaps because the columns don't align or because I'm very tired.

You say:
Finally, for a way to look up a date, pull in the total amount of interest + the interest for that month/30 * extra days for that month:

In my sheet I do not have your columns A through H, so you'll have to adjust these formulas based on where you want to put them...

Everything below this doesn't work for me. I was hoping if I included a more complete spreadsheet you could help me understand what I'm entering wrong.

=VLOOKUP(E5,J2:O19,6)

I'm not sure exactly what column I reference as "6".

thank you,

ZZ_Loan.Calculator-Update.1.1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Payment Calculator							v 1.0		PaymentApply Date	Principal + Interest	Additional Principal	Interest Only	OtherFees	Interest Fee	Zero Out	InterestAccrued	Interest Total
2										1/1/2009			833.33				833.33	833.33
3		Loan #								2/1/2009			833.33				833.33	1,666.66
4		Inputs				Quarter Close Backward				3/1/2009			833.33				833.33	2,499.99
5			Loan Amount	$100,000.00			Close Date	9/2/2009		4/1/2009			833.33				833.33	3,333.32
6			Annual Interest Rate	10.00%			Days of Interest	1		5/1/2009			833.33				833.33	4,166.65
7			Term of Loan in Years	10			Beg Principal Balance	$100,000.00		6/1/2009			833.33				833.33	4,999.98
8			First Payment Date	1/1/2009			Paydowns	$980.41		7/1/2009	1,321.51					0.00	833.33	5,833.31
9			Frequency of Payment	Monthly			End Principal Balance	$99,019.59		8/1/2009	1,321.51					0.00	829.27	6,662.58
10			Starting Principal	Not Used Yet			Net Principle Due	$3,580.03		9/1/2009			829.27				825.16	7,487.74
11							Interest Due & Accrued			10/1/2009	1,321.51					0.00	825.16	8,312.90
12							Interest Earned (cummulative)	$7,377.18		11/1/2009	1,321.51					0.00	821.03	9,133.93
13		Summary (with no extra payments)					Interest pymts (cummulative)	$7,350.66									816.86	9,950.79
14			Number of Payments	120			Net Interest Receivable	$26.52									816.86	10,767.65
15			Rate (per period)	0.8333%													816.86	11,584.51
16			Payment (per period)	$1,321.51		Arbitrary Payoff Date											816.86	12,401.37
TEST

 

[AnalyticsGuy7]

The column 6 is my new column O which is a running sum of the interest paid at each month. Recall that I entered column N which does my formulaic calculations of the interest for that month (pulls off of my "lookup" sheet and adjusts depending on whether principal + interest was paid at the last month) and then column O does a running sum of those amounts.

 

[AnalyticsGuy7]

It looks like you have done that to columns Q and R, but I have them replace the current columns N and O. Sorry that wasn't clear.

 

[fragelracken]

I guess that's where I'm confused. Colomn O would be column 15, but you say it's column 6. That is what I don't understand. I'm using this:

=VLOOKUP(H5,J2:R19,18)

but it doesn't work. It's the last number that I don't understand what "col_index_num" is supposed to be. I keep getting #REF for that no matter what number I put there.

Sorry I'm not getting it.