This is an absolutely awesome video - I didn't even know the SEQUENCE and SCAN functions existed before this. Such a clear walk though though - thank you so much for this!
I fiddled around with it and you can use for the endbalance the following (my example, not mapped to the video example): =SCAN(C7,-E21#-INDEX(G21:G381,B21#),SUM) where C7 = BegBal -E21# = array of principals G21:G381 - is my area/array of Extra Prinicipal Payments B21# is the array with the periods So the INDEX pulls, based on an array, the extra payment out of a cell area which is not stored in an array. Caveat: Due to extra payments you break out of the regular payment schedule (annuity). But you can circumvent that by changing the Interest and principal calculation. I managed doing this by using the functions MAP with a LAMBDA function, where I basically use a MIN function inside, in order to close the payment schedule with 0 as an Ending balance. It's possible, but took me a while to figure out. Cheers Georg
Excellent. Maybe, if you have enough latin followers, in little corner of the video, you should write the function name in spanish. Actually, I understand, but you can increase the scope of the channel. i like a lot the fast explanation. thank so much 🙏👏👍
I am doing the Scan formula exactly as shown (=SCAN(0,B10#,SUM)) and all I can get in the scan column is "#NAME?" I cannot figure out what I am doing incorrectly. After doing some internet research, I replaced the word SUM with LAMBDA(a,b,SUM(a+b) and then it worked perfectly. Does this mean that I have a different or older version of excel? Or really, what does this indicate? Thanks.
@@ciaucia156 Same here - currently if you F1 on =SCAN it tells you to use =LAMBDA. Yet again this is still in Beta or Insiders only availability and it will be several months before this is in "Live / General" release Excel. Wish all experts would qualify such post with "This is available only in Beta / Insider Excel versions and may therefore not be available to you yet".
This is a great tutorial. However, I'm having a huge issue with the "Ending Balance" formula. When I type in "SUM", it wants me to add more information within the formula. If I simply close to brackets, I get "#NAME" error. I've been trying for 3 hours to fix this. But I just can't figure out what I'm doing wrong. My formula mimics yours to the letter. What am I doing wrong???
It is likely that your version of excel doesn't support such 'eta reduced lambda' functions (eg, using SUM as a function argument). In these cases, you can use LAMBDA(a,b,SUM(a+b)) instead of SUM as that argument, and it should provide the desired results. Hope it helps!
At the time of this video, the SCAN function is in the process of being rolled out to Excel 365 users. It won't be retroactively added to perpetual license versions of Excel such as Excel for Windows 2016, 2019, and so on. So, the #Name? error most likely means your version of Excel doesn't have the SCAN function or doesn't support eta reduced lambdas. You may be able to replace the SUM argument with the LAMBDA equivalent.
perfect, please create a dynamic amortization table with Interest only payment option. it would be great help. Thanks in advance Eg. Loan Amount 1,200,000 Loan Rate 12% Annually Loan Term 20 Interest Only Payment Period 10 Extra payment 3000 Following option are required in column 0 Payment No. 1 Opening Balance. 2 Principal. 3 Schudel Monthly Payment 4 Interest. 5 cumulative Interest. 6 Extra Payment. 7 Ending Balance.
This is an absolutely awesome video - I didn't even know the SEQUENCE and SCAN functions existed before this. Such a clear walk though though - thank you so much for this!
Love it! How do you factor in extra monthly payments when using PPMT and IPMT while still keeping the spill ranges and dynamic arrays?
This is excellent and thank you very much for this video! Short, sweet and to the point.
Glad you enjoyed it!
I loved your approaches
Great explanation. How do you add an Extra Principal Payment column using Dynamic Arrays? I'm getting Circular References. Thanks.
I fiddled around with it and you can use for the endbalance the following (my example, not mapped to the video example): =SCAN(C7,-E21#-INDEX(G21:G381,B21#),SUM)
where
C7 = BegBal
-E21# = array of principals
G21:G381 - is my area/array of Extra Prinicipal Payments
B21# is the array with the periods
So the INDEX pulls, based on an array, the extra payment out of a cell area which is not stored in an array.
Caveat: Due to extra payments you break out of the regular payment schedule (annuity). But you can circumvent that by changing the Interest and principal calculation. I managed doing this by using the functions MAP with a LAMBDA function, where I basically use a MIN function inside, in order to close the payment schedule with 0 as an Ending balance.
It's possible, but took me a while to figure out.
Cheers Georg
This is really cool! However unfortunately you can’t use it within a table, is there anyway set this up to where you can also use filter feature?
Excellent. Maybe, if you have enough latin followers, in little corner of the video, you should write the function name in spanish. Actually, I understand, but you can increase the scope of the channel. i like a lot the fast explanation. thank so much 🙏👏👍
What if I want to pay say $5000 extra in a certain month? How can I account for that in this amortization schedule?
why am i coming back with a #NAME? for the running balance???
I am doing the Scan formula exactly as shown (=SCAN(0,B10#,SUM)) and all I can get in the scan column is "#NAME?" I cannot figure out what I am doing incorrectly. After doing some internet research, I replaced the word SUM with LAMBDA(a,b,SUM(a+b) and then it worked perfectly. Does this mean that I have a different or older version of excel? Or really, what does this indicate?
Thanks.
it happens to me too.
form of "eta reduced lambda" is used here (currently available only in beta chanell).
btw, you could use just LAMBDA(a,b,a+b)
Thanks, yes, I replaced with the shortened formula and it still worked fine.@@ciaucia156
@@ciaucia156 Same here - currently if you F1 on =SCAN it tells you to use =LAMBDA. Yet again this is still in Beta or Insiders only availability and it will be several months before this is in "Live / General" release Excel. Wish all experts would qualify such post with "This is available only in Beta / Insider Excel versions and may therefore not be available to you yet".
I have that same problem, but using LAMBDA just returns #REF. :(
very good
This is a great tutorial. However, I'm having a huge issue with the "Ending Balance" formula. When I type in "SUM", it wants me to add more information within the formula. If I simply close to brackets, I get "#NAME" error. I've been trying for 3 hours to fix this. But I just can't figure out what I'm doing wrong. My formula mimics yours to the letter. What am I doing wrong???
It is likely that your version of excel doesn't support such 'eta reduced lambda' functions (eg, using SUM as a function argument). In these cases, you can use LAMBDA(a,b,SUM(a+b)) instead of SUM as that argument, and it should provide the desired results. Hope it helps!
@@ExcelU That would make sense.
Tried to replicate your amortisation spreadsheet but my version of Excel does not support the SCAN function. Is the SCAN function still in beta mode?
Yes ... in other words SCAN is not fully rolled out at this time to 365 subscribers.
show value error, please help
I can't get the scan function to work. I enter =SCAN(0,the array,sum) and I get #Name?
At the time of this video, the SCAN function is in the process of being rolled out to Excel 365 users. It won't be retroactively added to perpetual license versions of Excel such as Excel for Windows 2016, 2019, and so on. So, the #Name? error most likely means your version of Excel doesn't have the SCAN function or doesn't support eta reduced lambdas. You may be able to replace the SUM argument with the LAMBDA equivalent.
in the function syntax, use LAMBDA(a,b,a+b), it will work
perfect, please create a dynamic amortization table with Interest only payment option. it would be great help. Thanks in advance
Eg. Loan Amount 1,200,000
Loan Rate 12% Annually
Loan Term 20
Interest Only Payment Period 10
Extra payment 3000
Following option are required in column
0 Payment No. 1 Opening Balance. 2 Principal. 3 Schudel Monthly Payment 4 Interest. 5 cumulative Interest. 6 Extra Payment. 7 Ending Balance.