Adding Tolerances to Excel LAMBDA functions

Question

I created a LAMBDA function called LOANAMT to calculate loan amounts recursively for a situation in which you need to borrow to fund the loan payment (yes, I know this can be solved algebraically - I'm trying to learn about LAMBDA). I incorporated a tolerance check as my escape clause; if the next round of interest calculations is very close to the previous round, the LAMBDA exits. This worked fine with a hard-coded tolerance level of 0.001:

=LAMBDA(opening_balance, base_rate, [interest],
    LET(
        _int, IF(ISOMITTED(interest), 0, interest),
        _new_close, opening_balance + _int,
        _new_int, _new_close * base_rate,
        _closing_balance, IF(ABS(_new_int-_int)<0.001, _new_close,LOANAMT(opening_balance,base_rate,_new_int)),
        _closing_balance
    )
)

Gave me 106.38290 where opening_balance = 100, base_rate = 6%, which approximately agrees with the algebraic solution.

However, when I tried incorporating the tolerance as a parameter of the LAMBDA so that it could be adjusted easily, I got a #NUM error.

=LAMBDA(opening_balance, base_rate, tolerance, [interest],
    LET(
        _int, IF(ISOMITTED(interest), 0, interest),
        _new_close, opening_balance + _int,
        _new_int, _new_close * base_rate,
        _closing_balance, IF(ABS(_new_int-_int)<tolerance, _new_close,LOANAMT2(opening_balance,base_rate,_new_int)),
        _closing_balance
    )
)

Could anyone explain what's going wrong and help me fix this? Thanks.

Aidan · Accepted Answer · 2022-09-23 10:30:26Z

1

The second version doesn't pass the tolerance value to the recursive call to LOANAMT2, hence the #NUM! error.

This works:

LOANAMT2=LAMBDA(opening_balance, base_rate, tolerance, [interest],
    LET(
        _int, IF(ISOMITTED(interest), 0, interest),
        _new_close, opening_balance + _int,
        _new_int, _new_close * base_rate,
        _closing_balance, IF(ABS(_new_int-_int)<tolerance, _new_close,LOANAMT2(opening_balance,base_rate,tolerance,_new_int)),
        _closing_balance
    )
)

answered Sep 23, 2022 at 10:30

Aidan

1,8101 gold badge16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding Tolerances to Excel LAMBDA functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related