LET Function stuck

[Holger]

Hi there,

I'm trying to solve problems with the LET function and with a new challenge I'm stuck again. I have some investment assumptions, for which I would like to create a table that shows when how much interest is earnt/paid. Screenshot 1 has the assumption, Screenshot 2 what I want to achieve.

Interest days are the diff between payment days (and in the case of the first payment diff between payment date and investment start). Invest1Pay is. range B11:B14 and contains the interest payment dates.

As the second screenshot shows, I can't get the loop working. The current formula is as follows:

=LET(
invest,B5,
start, B6,
end, B8,
a_rate, B9,
d_rate, a_rate/365,
p_day, VSTACK(Invest1Pay),
int_days,p_day-p_day,
p_count,COUNT(p_day),
int_paid, d_rate*int_days*invest,
VSTACK(
VSTACK({"Amount","Start","End","Annual Rate","Daily Rate","Pay Date","Interest Days","Interest Paid"}),
HSTACK(invest, start,end, a_rate, d_rate,p_day,int_days, int_paid)
))


Thank You so much everyone for assisting on this.

Cheers H

 

[Holger]

Hi,

so I made some progress and now have the cum interest paid and cum number of interest days.

What I still need assistance with is:

* how do I change column <<Count Days" to show only the number of days for that period. First row 36 is okay (from start date to first payment date). 2nd row 128 needs to deduct previous period days so result would be 92 days (from 05/11/2024 to 05/02/2025.......
* same for interest, how do I show interest for period not only cum interest.
* how to I get ride of #N/A in col Amount, Start, End, Daily Rate?

HeinWealthFile.xlsx
	S	T	U	V	W	X	Y	Z
32	Count	Amount	Start	End	Daily Rate	Pay Date	Count Days	Cum Int Paid
33	1	800,000	30/09/2024	5/06/2025	0.05%	5/11/2024	36	13,414
34	2	#N/A	#N/A	#N/A	#N/A	5/02/2025	128	47,693
35	3	#N/A	#N/A	#N/A	#N/A	5/05/2025	217	80,855
36	4	#N/A	#N/A	#N/A	#N/A	5/06/2025	248	92,405
Investments

Cell Formulas
Range	Formula
S32:Z36	S32	=LET( invest,B5, start,B6, end,B8, d_rate,B9/365, p_day,VSTACK(Invest1Pay), npers,COUNT(p_day), pers,SEQUENCE(npers), d_count,p_day-start, int_paid,d_rate*invest*d_count, days,SCAN(start,d_count,LAMBDA(a,b,a-b)), VSTACK( VSTACK({"Count","Amount","Start","End","Daily Rate","Pay Date","Count Days","Cum Int Paid"}), HSTACK(pers,invest,start,end,d_rate,p_day,d_count,int_paid) ))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Invest1Pay	=Investments!$B$11:INDEX(Investments!$B$11:$B$20,SUMPRODUCT(--(Investments!$B$11:$B$20<>"")))	S32

thanks for any help

 

[Holger]

Hi Everyone,

so slowly solving this by myself step by step. Now I have got this far and two minor tasks outstanding:

1. how do I repeat start and en with same date for all rows in the array? I managed to achieve this for invest and d_rate by defining inv and rate. However this does not work for dates in start and end>

HeinWealthFile.xlsx
	S	T	U	V	W	X	Y	Z
32	Count	Amount	Start	End	Daily Rate	Pay Date	Int Days	Int Paid
33	1	800,000	30/09/2024	5/06/2025	0.05%	5/11/2024	36	13,413.70
34	2	800,000	#N/A	#N/A	0.05%	5/02/2025	92	34,279.45
35	3	800,000	#N/A	#N/A	0.05%	5/05/2025	89	33,161.64
36	4	800,000	#N/A	#N/A	0.05%	5/06/2025	31	11,550.68
Investments

Cell Formulas
Range	Formula
S32:Z36	S32	=LET( invest,B5, start,B6, end,B8, d_rate,B9/365, p_day,VSTACK(Invest1Pay), npers,COUNT(p_day), pers,SEQUENCE(npers), inv,SEQUENCE(npers,,invest,0), rate, SEQUENCE(npers,,d_rate,0), help_count,DROP(VSTACK(start,p_day),-1), d_count,p_day-start, int_days, p_day-help_count, int_paid,d_rate*invest*int_days, VSTACK( VSTACK({"Count","Amount","Start","End","Daily Rate","Pay Date","Int Days","Int Paid"}), HSTACK(pers,inv,start,end,rate,p_day,int_days, int_paid) ))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Invest1Pay	=Investments!$B$11:INDEX(Investments!$B$11:$B$20,SUMPRODUCT(--(Investments!$B$11:$B$20<>"")))	S32

Any help would be greatly appreciated.

 

[Holger]

So here is the solution to my own question as everything works now just fine. I'm sure that this is not the most efficient way so anyone willing to assist in make this more efficient, would be greatly appreciated as it allows to learn.

HeinWealthFile.xlsx
	S	T	U	V	W	X	Y	Z
32	Count	Amount	Start	End	Daily Rate	Pay Date	Int Days	Int Paid
33	1	800,000	30/09/2024	5/06/2025	0.05%	5/11/2024	36	13,413.70
34	2	800,000	30/09/2024	5/06/2025	0.05%	5/02/2025	92	34,279.45
35	3	800,000	30/09/2024	5/06/2025	0.05%	5/05/2025	89	33,161.64
36	4	800,000	30/09/2024	5/06/2025	0.05%	5/06/2025	31	11,550.68
Investments

Cell Formulas
Range	Formula
S32:Z36	S32	=LET( invest,B5, start,B6, end,B8, d_rate,B9/365, p_day,VSTACK(Invest1Pay), npers,COUNT(p_day), pers,SEQUENCE(npers), inv,SEQUENCE(npers,,invest,0), rate, SEQUENCE(npers,,d_rate,0), s, SEQUENCE(npers,,start,0), e, SEQUENCE(npers,,end,0), help_count,DROP(VSTACK(start,p_day),-1), d_count,p_day-start, int_days, p_day-help_count, int_paid,rate*inv*int_days, VSTACK( VSTACK({"Count","Amount","Start","End","Daily Rate","Pay Date","Int Days","Int Paid"}), IFNA(HSTACK(pers,inv,s,e,rate,p_day,int_days, int_paid),0) ))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Invest1Pay	=Investments!$B$11:INDEX(Investments!$B$11:$B$20,SUMPRODUCT(--(Investments!$B$11:$B$20<>"")))	S32

 

[djclements]

The IF function can be used to broadcast a horizontal vector downwards by supplying the logical_test argument with a vertical vector of TRUE values, and since any numeric value other than zero is interpreted as TRUE, the pers variable can be used in this scenario: IF(pers,HSTACK(invest,start,end,d_rate)).

=LET(
    invest, B5,
    start, B6,
    end, B8,
    d_rate, B9/365,
    p_day, Invest1Pay,
    pers, SEQUENCE(ROWS(p_day)),
    int_days, p_day-DROP(VSTACK(start,p_day),-1),
    int_paid, d_rate*invest*int_days,
    VSTACK(
        {"Count","Amount","Start","End","Daily Rate","Pay Date","Int Days","Int Paid"},
        HSTACK(pers,IF(pers,HSTACK(invest,start,end,d_rate)),p_day,int_days,int_paid)
    )
)

I hope that helps!

 

[Holger]

The IF function can be used to broadcast a horizontal vector downwards by supplying the logical_test argument with a vertical vector of TRUE values, and since any numeric value other than zero is interpreted as TRUE, the pers variable can be used in this scenario: IF(pers,HSTACK(invest,start,end,d_rate)).

=LET(
    invest, B5,
    start, B6,
    end, B8,
    d_rate, B9/365,
    p_day, Invest1Pay,
    pers, SEQUENCE(ROWS(p_day)),
    int_days, p_day-DROP(VSTACK(start,p_day),-1),
    int_paid, d_rate*invest*int_days,
    VSTACK(
        {"Count","Amount","Start","End","Daily Rate","Pay Date","Int Days","Int Paid"},
        HSTACK(pers,IF(pers,HSTACK(invest,start,end,d_rate)),p_day,int_days,int_paid)
    )
)

I hope that helps!

Awesome DJLet that works and is much, much shorter. Appreciate the time to look at this and your feedback.

Cheers H