How do I use LET, SEQUENCE, MAKEARRAY, and other functions to calculate monthly interest in Numbers?

Taking fuller advantage of LET:

=LET(Principal,Inputs::B1,Rate,Inputs::B2,Months,SEQUENCE(Inputs::B3),Principal*(1+Rate/12)^Months)

The old way would be something like this:

=Inputs::$B$1*(1+Inputs::$B$2/12)^(ROW()−1)

Still works, and is more succinct. But it isn't as easy to read and needs to be filled down into the rows below instead of an automatic "spill."

SG

arp1007 wrote:

I solved my problem I wrote this formula correctly and it spills in 12 rows as month and calculates correct value of interest

LET(Principal,B1,Rate,B2,Months,B3,Principal×(1+B2÷12)^SEQUENCE(B3))

Nice! Upvote! Your solution calculates the ending balance for each period. Here it is juxtaposed against an "old school" schedule to check its calculations. It's a lot easier, just one formula in one column!

SG

I don't understand the point of LET() here.

In general, LET() allows you to assign values (or calculations) to variable names and use those friendly names in a calculation rather than the more complex definition. So far, so good.

Your LET() formula defines three variables, namely, Principal, Rate and Months, however, you then ignore two of those three and never use them again, opting, instead, for the direct reference.

If you want to use LET(), then your formula should be:

=LET(Principal,B1,Rate,B2,Months,B3,Principal×(1+Rate÷12)^SEQUENCE(Months))

Now it's using the variable names defined by LET() rather than direct references to the cells. Not a big difference in this particular case, but if you're going to use LET() to define a variable you might as well use it :)

> Using Let function is more clean way just using one formula in one cell.

I realize that. My point was that you were using LET() to define a variable that you were never using.

For example, you're using LET to define Rate as the value of B2, but later in your function you directly reference B2 rather than the LET variable. Likewise you define Months as B2, but later reference B3 rather than the Months variable.

It isn't entirely clear what you're trying to do here - which cells have formulas, which cells have values, and what is wrong vs. right.

However, the first thing I see is that your SEQUENCE() function is incorrect.

The definition of SEQUENCE() is:

SEQUENCE(number of rows, number of columns, start, step)

Where start and stop are optional.

However, you're passing in two:

SEQUENCE(B3,12)

Which will create a 12 x 12 grid of values. Is that what you actually want? I can't see how that would relate to the rest of the sheet. But that may explain why Numbers is asking for 4 more columns - your table isn't big enough to hold the 12 columns and you need to add more.

Either way, it would help if you could clarify what it is you're trying to achieve here, or are you just trying different ways to get the same result (that's entirely fair, but it would help to have that understanding up front).