@@ProfectusLearning I made one using VBA. I would like to know if it is possible using dynamic array or lamda functions. It should not only find remaining inventory,but also adjust each sale against purchase on fifo basis to identify transaction wise profit loss similar to what you do in share Market transaction.
That can be done using path-dependent present value computation. You can even factor in ad-hoc prepayments. But we would need to use the actual mathematical equation rather than the built-in function. I wanted to make a video on that but since it involved explaining a lot of mathematics and MS Excel, I still planning how to execute it.
Hi is it possible to use the sequence formular in a scenario where interest in charged on monthly basis but payment are made after every 3 month. Meaning the payment column should have zero in 1st two rows then a constant number in 3rd row followed by zero in the next two rows then a constant number again.
There are two options: (i) One option is to use MOD() function where you check if MOD(MonthNum, 3) = 0 then payment should comes else zero. But I wouldn't recommend this option. The FV calculation gets pretty complicated (ii) The other option is make the model on quarterly basis but change the interest rate as follows Qtly int rate = (1 + int_rate/12)^(12/4) - 1
individual method took 1 minute, cramming them together into 1 lambda formula took more than 5 mins. This isn't a practical example that will highlight the advantages of the lambda function..
@@ProfectusLearning Oh my bad, so the point of the video is to just show that this can be done the hard way using lambda formula not necessarily because lambda is the best way to go in solving a dynamic debt amort sched? Ok.....
@@lactobacillusshirotastrain8775 I am sure, you would also argue that why waste tens of thousands of hours developing a software like excel to do calculations, when calculators can already do that faster.
you're sure? you just proved my point. It's better to use a spreadsheet than a calculator for tasks such as DYNAMIC debt amort scheduling.. Is lambda better at solving the same task vs creating a few helper columns to accomplish the same goal?
@@ProfectusLearning Im not saying lambda is waste of time and doesn't do you any good. I'm saying you could have chosen a better example. One that could highlight the advantages of the lambda function.
Mind blowing method.
Very cool that CHOOSE function combines columns like that.
Very well presented Video Prof Viz!! Great job
Well explained. Thanks for sharing Viz!
Can you make a video on creating fifo adjuster for inventory.
Sure.
@@ProfectusLearning
I made one using VBA. I would like to know if it is possible using dynamic array or lamda functions. It should not only find remaining inventory,but also adjust each sale against purchase on fifo basis to identify transaction wise profit loss similar to what you do in share Market transaction.
@@petersimon7568 It will be possible. All mathematical calculation that can be done using VBA should be doable using Lambda
How can you self reference for the beginning balance in case of variable interest rates/payments?
That can be done using path-dependent present value computation. You can even factor in ad-hoc prepayments. But we would need to use the actual mathematical equation rather than the built-in function. I wanted to make a video on that but since it involved explaining a lot of mathematics and MS Excel, I still planning how to execute it.
Hi is it possible to use the sequence formular in a scenario where interest in charged on monthly basis but payment are made after every 3 month.
Meaning the payment column should have zero in 1st two rows then a constant number in 3rd row followed by zero in the next two rows then a constant number again.
There are two options:
(i) One option is to use MOD() function where you check if MOD(MonthNum, 3) = 0 then payment should comes else zero. But I wouldn't recommend this option. The FV calculation gets pretty complicated
(ii) The other option is make the model on quarterly basis but change the interest rate as follows
Qtly int rate = (1 + int_rate/12)^(12/4) - 1
Many thanks the option two worked perfectly
Wouldn't HSTACK be simpler than using CHOOSE?
Indeed, it would be better. If I remember correctly, HSTACK didn’t exist when I made this video.
How to download
Pls share me this excel file
individual method took 1 minute, cramming them together into 1 lambda formula took more than 5 mins. This isn't a practical example that will highlight the advantages of the lambda function..
Lambda approach will always take more time when creating. The advantage comes when you reuse it.
@@ProfectusLearning Oh my bad, so the point of the video is to just show that this can be done the hard way using lambda formula not necessarily because lambda is the best way to go in solving a dynamic debt amort sched? Ok.....
@@lactobacillusshirotastrain8775 I am sure, you would also argue that why waste tens of thousands of hours developing a software like excel to do calculations, when calculators can already do that faster.
you're sure? you just proved my point. It's better to use a spreadsheet than a calculator for tasks such as DYNAMIC debt amort scheduling.. Is lambda better at solving the same task vs creating a few helper columns to accomplish the same goal?
@@ProfectusLearning Im not saying lambda is waste of time and doesn't do you any good. I'm saying you could have chosen a better example. One that could highlight the advantages of the lambda function.