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

 

[AnalyticsGuy7]

The 6 is a relative reference. Note the range that it is looking at J2:R19 so J becomes column 1. I think you could keep your data in P and Q if you'd like, but you'd adjust the number to 8 (8 columns away from J)

 

[fragelracken]

OK, I got it. I would use "8" because it's the 8th column inside the vlookup range.

Working to complete it now.

 

[fragelracken]

Thanks a-lot AnalyticsGuy7.

There appears to be only 1 last issue...

When calculating the daily interest in the final steps you use:

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

that's great; however, there are 2 scenarios: The 1st is the payment was interest only in which case the value for the Interest would be correct when calculating extra days of interest. The 2nd is the payment is a principal payment in which case it's interest rate is actually at a lower rate for the extra days. Example: Say the close date is 10/15/09. The Interest for 10/1/09 would be $825.16, but for calculating from 10/1/09 to 10/15/09 it would be 821.03 / 30 * 14 (this is because the principal was paid on 10/1 so the interest gets decreased from 10/2 through 11/1).

Any suggestions?

Thanks again, you have been extremly helpful!

 

[AnalyticsGuy7]

I have a quick answer and then will have to try and get to it in the morning if you don't figure it out.

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

Try

=(INDEX(N2:N19,MATCH(E5,J2:J19)+1)/30)*E7

This will utilize the next month's interest rate, which will either be the same or decreased if there was a Principal + Interest Paid.

Good luck!

 

[fragelracken]

That did the trick! Thanks a-million!