Create dynamic debt amortization schedule with just one function using Lambda

In this video, you will learn how to use the Lambda function in MS Excel to create a dynamic debt amortisation schedule. You an also follow side by side with the excel file available in the following link:
www.profectusacademy.com/prac... (2nd item in the list)
As part of this, you will learn to work with various function and features of MS Excel
- FV function
- PMT function
- LET function
- LAMBDA function
- Sequence function
- Working with spilled arrays and many more
In this video, you will first learn how to use the future value or FV function to automatically find the ending balance of an amortizing loan. You will then calculate the interest amount of every period for an amortizing loan. Finally, you will learn to calculate

[@syedaneesdurez8766]

Mind blowing method.

[@xlrobot]

Very cool that CHOOSE function combines columns like that.

[@YouExcelTutorials]

Well explained. Thanks for sharing Viz!

[@sushilahariharan]

Very well presented Video Prof Viz!! Great job

[@user-rr1dh4hb5s]

How can you self reference for the beginning balance in case of variable interest rates/payments?

[@ProfectusLearning]

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.

[@petersimon7568]

Can you make a video on creating fifo adjuster for inventory.

[@ProfectusLearning]

Sure.

[@petersimon7568]

@@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.

[@ProfectusLearning]

@@petersimon7568 It will be possible. All mathematical calculation that can be done using VBA should be doable using Lambda

[@abdangembe4339]

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.

[@ProfectusLearning]

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

[@abdangembe4339]

Many thanks the option two worked perfectly

[@bhavneshparikh8283]

How to download

[@david_allen1]

Wouldn't HSTACK be simpler than using CHOOSE?

[@ProfectusLearning]

Indeed, it would be better. If I remember correctly, HSTACK didn’t exist when I made this video.

[@syedaneesdurez7197]

Pls share me this excel file

[@lactobacillusshirotastrain8775]

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]

Lambda approach will always take more time when creating. The advantage comes when you reuse it.

[@lactobacillusshirotastrain8775]

@@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.....

[@ProfectusLearning]

@@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.

[@lactobacillusshirotastrain8775]

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?

[@lactobacillusshirotastrain8775]

@@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.