Compound interest since specific date

[Trotzali]

I would like to figure out the total payout figure for a loan.
I have the initial amount in column b
Date of loan in column c
Interest rate in column d

I need the total amount of the loan today with compounding interest without having made any payments.

For instance:

$10k loan from 6th July 2013
No payments made
10% interest - compounding.
What do I owe today?

I would also like to be able to change whether the interest was calculated daily, weekly, monthly etc

Thank you!

 

[MickG]

Perhaps something like this:-
[TABLE="width: 513"]
<colgroup><col width="170" style="width: 128pt; mso-width-source: userset; mso-width-alt: 6058;"> <col width="175" style="width: 131pt; mso-width-source: userset; mso-width-alt: 6229;"> <col width="161" style="width: 121pt; mso-width-source: userset; mso-width-alt: 5717;"> <col width="177" style="width: 133pt; mso-width-source: userset; mso-width-alt: 6286;"> <tbody>[TR]
[TD="class: xl63, width: 170, bgcolor: transparent"]Loan[/TD]
[TD="class: xl63, width: 175, bgcolor: transparent"]Int Rate [/TD]
[TD="class: xl63, width: 161, bgcolor: transparent"]Starting Date[/TD]
[TD="class: xl63, width: 177, bgcolor: transparent"]Today Date[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]10000[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10%[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]06/07/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]01/01/2018[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl66, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl66, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl66, bgcolor: #BFBFBF"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]No of days[/TD]
[TD="class: xl63, bgcolor: transparent"]No of weeks[/TD]
[TD="class: xl63, bgcolor: transparent"]No of Mths[/TD]
[TD="class: xl63, bgcolor: transparent"]No of years[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]=D2-C2[/TD]
[TD="class: xl63, bgcolor: transparent"]=(D2-C2)/7[/TD]
[TD="class: xl63, bgcolor: transparent"]=(D2-C2)/365*12[/TD]
[TD="class: xl63, bgcolor: transparent"]=(D2-C2)/365[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1640[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]234.2857143[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]53.91780822[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4.493150685[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl66, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl66, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl66, bgcolor: #BFBFBF"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Daily rate[/TD]
[TD="class: xl63, bgcolor: transparent"]week rate[/TD]
[TD="class: xl63, bgcolor: transparent"]Mth Rate[/TD]
[TD="class: xl63, bgcolor: transparent"]Yearly Rate[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]=B2/365[/TD]
[TD="class: xl63, bgcolor: transparent"]=B2/52[/TD]
[TD="class: xl63, bgcolor: transparent"]=B2/12[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]0.000273973[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.001923077[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.008333333[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.1[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl66, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl66, bgcolor: #BFBFBF"] [/TD]
[TD="class: xl66, bgcolor: #BFBFBF"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Amount Owing DayRate[/TD]
[TD="class: xl63, bgcolor: transparent"]Amount Owing WkRate[/TD]
[TD="class: xl63, bgcolor: transparent"]Amount Owing MthRate[/TD]
[TD="class: xl63, bgcolor: transparent"]Amount Owing YrRate[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]=A2*(1+A11)^A6[/TD]
[TD="class: xl63, bgcolor: transparent"]=A2*(1+B11)^B6[/TD]
[TD="class: xl63, bgcolor: transparent"]=A2*(1+C11)^C6[/TD]
[TD="class: xl63, bgcolor: transparent"]=A2*(1.1)^D6[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]15671.41924[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15684.95351[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15643.23182[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15345.58933[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Description	Account	Start	Calc Date	Amount	Rate	Type	Future Value	Interest
2			6-Jul-13	1-Jan-18	$10,000.00	10%	Monthly	15,642.71	5,642.71
3			1-Jan-17	31-Dec-17	$200,000.00	12%	Yearly	224,000.00	24,000.00
4
5
6		Daily
7		Monthly
8		Weekly
9		Yearly
1bb

Cell Formulas
Range	Formula
E2	='1a'!A2
H2	=CHOOSE(MATCH(G2,$B$6:$B$9,0),FV(F2/365,(D2-C2+1),0,-E2),FV(F2/12,(D2-C2+1)/30.4375,0,-E2),FV(F2/52.1429,(D2-C2+1)/7,0,-E2),FV(F2,(D2-C2+1)/365,0,-E2))
I2	=H2-E2

You wanted to " to be able to change whether the interest was calculated daily, weekly, monthly etc "

Cell G2 has a drop down re list B6:B9