CODEWITHSUNDEEP
excel-formulaexcel-lambda
Jack Harman
Jack Harman

Reputation: 1

Adding Tolerances to Excel LAMBDA functions

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.

Upvotes: 0

Views: 59

Answers (1)

Aidan
Aidan

Reputation: 1750

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
    )
)

Upvotes: 1

Related Questions