Dynamic Compound Interest Formula

[plkdti]

Apologies if some iteration of this question has already been asked, but I wasn't able to find an exact analogue to my situation.


I'd like to create a formula that calculates compound interest on a note based on user inputs for: (i) principal, (ii) annual interest rate, (iii) interest accrual period (three choices--annual/monthly/daily), (iv) day count basis (three choices--Actual/Actual (or just leaving it blank), Actual/360, Actual/365), (v) interest start date, and (vi) interest end date.


My current formula is:
=IFERROR( [Principal] * (POWER(1 + ( [Annual Interest Rate] / IFERROR( IFS( [Interest Accrual Period] ="Monthly", 12, [Interest Accrual Period] = "Daily", IF( [Day Count Basis] = "Actual/360", 360, 365), [Interest Accrual Period] = "Annual",1),1)),YEARFRAC( [Start Date] , [End Date], IFS(ISBLANK([Day Count Basis]),1,[Day Count Basis]="Actual/360",2,[Day Count Basis]="Actual/365",3))*IFERROR(IFS([Interest Accrual Period]="Monthly",12,[Interest Accrual Period]="Daily",IF([Interest Accrual Period]="Actual/360",360,365),[Interest Accrual Period]="Annual",1),1)))-[Principal]A2,0)


I know my formula is not properly accounting for leap years--so any suggestions on that would be great. But also looking to see if there are any problems with the rest of the formula. Not 100% the YEARFRAC function is doing what I want it to do.


Really appreciate your help!

Sample.xlsx
	C	D	E	F	G	H	I
63	Principal	Interest Rate	Interest Accrual Period	Day Count Basis	Start Date	End Date	Interest
64	$ 1,700,000.00	4%	Daily	Actual/360	1/1/20	1/1/22	$ 143,831.90
Pro Forma

Cell Formulas
Range	Formula
I64	I64	=IF($C$5=1,IFERROR(C64*(POWER(1 + (D64/IFERROR(IFS(E64="Monthly",12,E64="Daily",IF(F64="Actual/360",360,365),E64="Annual",1),1)),YEARFRAC(G64,H64,IFS(ISBLANK(F64),1,F64="Actual/360",2,F64="Actual/365",3))*IFERROR(IFS(E64="Monthly",12,E64="Daily",IF(F64="Actual/360",360,365),E64="Annual",1),1)))-C64,0))

 

[Dave Patton]

The correct calculation depends on the legal contract and law in your jurisdiction.

T202111a.xlsm
	C	D	E	F	G	H	I
62
63	Principal	Interest Rate	Interest Accrual Period	Day Count Basis	Start Date	End Date	Interest
64	1,700,000.00	0.04	Daily	Actual/360	01-01-20	01-01-22	140,924.86
65
66							731
67							2
68							$1,841,781.76
69							$1,838,720.00
70							$1,840,924.86
71
72
6a

Cell Formulas
Range	Formula
I64	I64	=IF($C$5=1,IFERROR(C64*(POWER(1 + (D64/IFERROR(IFS(E64="Monthly",12,E64="Daily",IF(F64="Actual/360",360,365),E64="Annual",1),1)),YEARFRAC(G64,H64,IFS(ISBLANK(F64),1,F64="Actual/360",2,F64="Actual/365",3))*IFERROR(IFS(E64="Monthly",12,E64="Daily",IF(F64="Actual/360",360,365),E64="Annual",1),1)))-C64,0))
I66	I66	=H64-G64
I67	I67	=DATEDIF(G64,H64,"y")
I68	I68	=FV(D64/365,I66,0,-C64,1)
I69	I69	=FV(D64,2,0,-C64,1)
I70	I70	=FV(D64,YEARFRAC(G64,H64,2),0,-C64,1)