Future Value of a one time investment a few years later?

[Joe Carter]

I don't use Excel's financial formulas. I've hit help, but can't figure it out. It's SIMPLE! So I feel stupid.

If I would invest in a company who can't pay me back right now, how much do they owe me. Actual example:
On 6/30/2022, invested $165,000. Interest rate is 9% (verbiage from the contract "
Interest is set at the fixed rate of nine percent (9.00%) per annum until this Note
is fully paid.").
How much does the company owe me today?

I used simple formulas:
Number of years = (Today()-6/30/2022)/365
Accrued Interest = $165K * 9% * Number of years = $28,113.
But I don't think that's right. Because after the first year the new debt would be $165K*1.09=$179.85K. 9% interest on that would be more.

Any help would be greatly appreciated.

 

[Dave Patton]

T202405.xlsm
	A	B	C
1
2		Amount	165,000.00
3		Rate	9.00%
4		Start	30-Jun-22
5		Date	30-Jun-24
6		Future Value	196,036.50
7
8	Year 1	30-Jun-23	179,850.00
9	Today	21-May-24	194,351.33
10
2d

Cell Formulas
Range	Formula
C6	C6	=FV(C3,2,,-C2)
C8	C8	=C2*(1+C3)
C9	C9	=C8*C3*(B9-B8+1)/365+C8

 

[Dave Patton]

An edit and a clearer example that would work with more years.

T202405.xlsm
	A	B	C	D	E	F
8		Amount	165,000.00		30-Jun-23	179,850.00
9		Rate	9.00%		21-May-24	194,306.98
10		Start	30-Jun-22
11		Date	21-May-24
12		Future Value	194,306.98
2dd

Cell Formulas
Range	Formula
F8	F8	=C8*(1+C$3)
F9	F9	=F8*(1+C9*(C11-E8)/365)
C12	C12	=LET(y,DATEDIF(C10,C11,"y"),FV(C9,y,,-C8)*(1+C9*(C11-EDATE(C10,y*12))/365))

T202405.xlsm
	A	B	C	D	E	F
1					Year
2		Amount	165,000.00		30-Jun-21	179,850.00
3		Rate	9.00%		30-Jun-22	196,036.50
4		Start	30-Jun-20		30-Jun-23	213,679.79
5		Date	21-May-24		21-May-24	230,856.13
6		Future Value	230,856.13
2dd

Cell Formulas
Range	Formula
F2	F2	=C2*(1+C$3)
F3:F4	F3	=F2*(1+C$3)
F5	F5	=F4*(1+C3*(C5-E4)/365)
C6	C6	=LET(y,DATEDIF(C4,C5,"y"),FV(C3,y,,-C2)*(1+C3*(C5-EDATE(C4,3*12))/365))

 

[Dave Patton]

C6 can be =LET(y,DATEDIF(C4,C5,"y"),FV(C3,y,,-C2)*(1+C3*(C5-EDATE(C4,y*12))/365))

 

[Joe Carter]

I googled the formula, FV=PV(1-r)(power of n) where r=interest rate and n is number of periods.
Since this is accrued annually, n=(Today()-6/30/2022)/365.
FV=PV*power(1+9%,n)
Right?

 

[Dave Patton]

Did you try the suggestion?
You did specify How much does the company owe me today?
N.B. You can copy the suggestion to a clean sheet. Click on the icon below the icon in the heading, move to your sheet and paste into cell A1.

T202405.xlsm
	A	B	C	D	E	F
1					Year
2		Amount	165,000.00		30-Jun-21	179,850.00
3		Rate	9.00%		30-Jun-22	196,036.50
4		Start	30-Jun-20		30-Jun-23	213,679.79
5		Date	21-May-24		21-May-24	230,856.13
6		Future Value	230,856.13
7
8		Amount	165,000.00		30-Jun-23	179,850.00
9		Rate	9.00%		21-May-24	194,306.98
10		Start	30-Jun-22
11		Date	21-May-24
12		Future Value	194,306.98
13
14		Amount	165,000.00
15		Rate	9.00%
16		Start	30-Jun-22
17		Date	30-Jun-24
18		Future Value	196,036.50	196,036.50
2dd

Cell Formulas
Range	Formula
F2,F8	F2	=C2*(1+C$3)
F3:F4	F3	=F2*(1+C$3)
F5	F5	=F4*(1+C3*(C5-E4)/365)
C6,C12	C6	=LET(y,DATEDIF(C4,C5,"y"),FV(C3,y,,-C2)*(1+C3*(C5-EDATE(C4,y*12))/365))
F9	F9	=F8*(1+C9*(C11-E8)/365)
C18	C18	=FV(C15,DATEDIF(C16,C17,"Y"),,-C14)
D18	D18	=C14*(1+C15)^2