Monthly compound interest calc in spill formula

Question

Well, ageing is real and I started building my spreadsheet to plan saving for retirement when I stumbeld in a problem I could not resolve for days until I threw the towel and conviced I need experts help.

I created a spill cell that is very dynamic and calculates the monthly interest rate considering applied to the Principal + Previous Month Contribution. In the initial months the formula calculates very close to the other tools I used to check on the internet, but the difference builds up quite badly and in 01/Jan/2030 the error is huge.

I'd appreciate if someone financial savy could cast an eye and see what is wrong.

https://imgur.com/xL8IkqK

Here is the formula I was building and a link for the file : https://file.io/Q93JqGdVaSsZ

=LET(
Year2MonthlyRate, LAMBDA(_YearRate,_NYears, ((1 + _YearRate) ^ (1 / 12))  ),

_Timeline, $D$4:$D$328,
_Contributed, $E$4:$E$328,

_NumMonthsPriorProjection, COUNTIF( _Contributed, ">0" ),
_NumMonthsToProject, COUNT( _Timeline ) - _NumMonthsPriorProjection,

_MonthsIndex, VSTACK(
   SEQUENCE( _NumMonthsPriorProjection, 1, 0, 0 ),
   SEQUENCE( _NumMonthsToProject, 1, 1, 1 )
),

_PlannedContribution, $B$5,
_TotalContribution, MAX( _Contributed ),
_MonthlyInterestPlanned,  Year2MonthlyRate( $B$4, _NumMonthsToProject /12  ),

IF( _MonthsIndex > 0,
   _PlannedContribution + (( _TotalContribution + ( (_PlannedContribution * _MonthsIndex) - _PlannedContribution ) ) * _MonthlyInterestPlanned   ),
0)
)

Answer 1

I'm guessing your desired goal is a table showing your projected future value at monthly intervals, given a starting point and the number of months you are projecting.

Focusing only on the point where you start to make contributions, at that time you have a starting value of $1,000, you can simplify what seems to me to be an overly complex formula to give you those results.

For example, given:

This formula in some cell:

=VSTACK(
    {"TIMELINE", "Planned Contribution", "Total"},
    HSTACK(
        EDATE(StartDate, SEQUENCE(months, , 0)),
        MAKEARRAY(months, 1, LAMBDA(r, c, Contrib)),
        FV(Return / 12, SEQUENCE(months), -Contrib, -Start, 0)
    )
)

will produce this result.
Note that the last value in the column agrees with your expected value, but it is occurring a month earlier. I suspect you may have entered values into whatever tool you were using differently, or your expected value cell is incorrect.