Hi Ross. While the CDF may appear to be incorrect (as CDFs typically end at one), it will give you the correct distribution without being skewed. Because I am using an approximate match and matching to the next lowest value, anything from 0 to 0.1667 matches to a roll of 1 and anything from 0.8333 to 1 matches to a roll of 6. If you build those same formulas and run a data table to simulate 1,000 dice rolls, you should see about 1/6th (16.67%) in each category (some variation due to sampling error).

The easiest way is to save a set of random numbers in a worksheet and use those in your VBA code. That is how I teach students to do it, and then you know the "random" values are not changing between iterations. I think there are also some ways in the VBA code itself, but some googling suggests that can be buggy.

Fair point - I missed cutting those first ten seconds - hopefully it didn't distract too much from the learning!

@@docbrownexcels It definitely does. Sets the tone initially so not a good idea.

Thank you! There is no add-in, but you do need to have more recent versions of Excel to get the newer functions like SEQUENCE (or even XLOOKUP). There is a whole new world of spilled-array formulas like SEQUENCE that makes Excel more powerful and quicker to use. I am not sure what version in particular you need, but Excel 365 is kept up-to-date with most (if not all) of the new features.

Thanks!@@docbrownexcels

In this model, to make that happen you would start by setting the required payment equal to the principal balance times the monthly interest rate (annual rate divided by 12). Then you would set the balloon payment equal to the principal balance, and you should get the payment schedule you are referring to. Make sure the extra payment is still set to zero.

I have heard of this problem once before, specifically with control+shift+3. From that case, it was specific to the computer and across all spreadsheets. I never heard of a resolution though. Sorry to not be more helpful.

Can you send me a screenshot of when it happens? You can email me dcbrown at arizona.edu. You might want to try to download a fresh copy too, as that is the first report I have heard of that.

In these cases, the interest rate and discount rate are both inputs, or assumptions. The discount rate is often thought of as the opportunity cost of capital, or the fair rate you could get in a competitive market. The interest rate would be the particular offer you are evaluating. For example, a car dealer may offer you an interest rate of 2% when the prevailing fair rate is 4%. In that case, the car dealer is subsidizing the financing in order to sell you the car, and you can use the model to figure out how much that subsidy is worth.

Hi Gary. With dynamic interest rates, the model calculates the payment each period based on the outstanding balance, number of periods remaining, and current interest rate. Without an Extra Payment and with a future rate change, that results in a constant payment until the rate changes, and then a different constant payment after the rate changes. When you add an Extra Payment, you are reducing the principal balance by more than expected, thereby reducing the required payment a little bit each period. That is why the payments go down a little each month, and then go down by a lot when the rate decreases. So the model is working, you just aren't getting the level payments that you get without the Extra Payment feature. That all said, you could adjust the model to only recalculate the payment when the interest rate changes. That would give the behavior I think you are expecting. The cost would be that when the interest rate changes could no longer be dynamic (cell C29). Those are trade-offs we often have to make when designing models.

@@docbrownexcels Thank you very much for your response. I seem to be stumbling a little on the formulas needed to make the adjustment. My goal, after adding dynamic interest rates, is to see how the extra payment decreases the Effective Loan Term just like it did in the prior video models. Even if I set the rate reduction to 0.00% (so that all rates remain at 4% just like the static interest rate we started with), the table still ends at period 240 instead of 214. If I add a $200 extra payment to each period, it only decreases the effective loan term by one month. I think I'm just not conceptually understanding. Would you be able to detail the steps I need to take to adjust the model for what I'm trying to do? Or is it not set up to do that? Thanks again! This series has been very helpful!

@@docbrownexcels Thank you very much for the response. I'm having a tough time adjusting the formula as you suggested, and I was hoping you can help explain exactly how to do that. What I'm trying to see is the amount of time the effective loan term would be reduced by if adding an extra payment AND after accounting for changing interest rates. I'm probably not conceptually understanding the calculations. In the model with a static interest rate, a $100 extra payment produces a 17.83 effective loan. However, after making the changes to incorporate the dynamic rates, the $100 extra payment still produces a 20 year effective loan term (19.92 if it's $200). This is if I change the rate reduction to 0%, effectively making it 4% the entire time just like it would have been if the rates weren't dynamic. Many thanks for making this series. It has been very helpful!

@@Garyket Hi Gary, sorry for my slow reply. The issue you are running into is that the payment is recalculating every period. So, when you add an extra payment of $100 or $200, the model is using that to reduce every future payment by just a little bit, rather than paying the loan off sooner. The concept is that by taking in the current balance, current rate, and remaining loan term, the payment always adjusts to pay off at the end of the original loan term. What I think you want is a model that calculates the payments at the beginning, and again when the loan rate adjusts, and holds those payment amounts constant and then reduces the loan term as you make overpayments. To make that work, start with the initial loan schedule in the model with the changing interest rate. Then I would link the payments in that tab to the original tab that had the overpayment incorporated. A combination of those two tabs should work for you, but let me know if you run into any big stumbling blocks.

Hi Pradeep. While it's true that CDFs will accumulate until they equal 1, in this case the last step occurs going from 0.83 to 1. Since we are using an approximate matching function, we have to choose whether to look for the next bigger or next smaller item. I chose to find the next smaller item, so I set up the CDF at using the lower side of each step, hence stopping at 0.83. It could have been set up to use the top side of each step, in which case the steps would have started at 0.17 instead of 0 and would then end at 1. Either set up will work.

@@docbrownexcels. I just felt If am missing anything there. Glad you replied. Thanks for the explanation.

You can download it at www.davidclaytonbrown.com/excel-resources

@@docbrownexcels Could I please receive the Excel file? I've already submitted a request. Thank you.

Hi Christine. Cash Flows from Investing will be added to the Change in Cash, which will then be added to Beginning Cash to get to Ending Cash. Typically, CAPEX will be negative (indicating that the company purchased fixed assets) which would make Cash Flows from Investing negative. CAPEX is then reflected as a use of cash that decreases Ending Cash. Hope that clarifies it for you.

@@docbrownexcels Ah, ok, thank you, that does clarify it. Thanks for the reply! A really excellent set of videos on financial statements.

To calculate a loan's NPV, you will always discount at the discount rate. The discount rate can be thought of as the opportunity cost of capital, or the fair, risk-based rate for borrowing the money. Importantly, the interest rate charged on the loan does not always match with the discount rate. When the interest rate is higher than discount rate, the NPV of the loan will be negative from the borrower's perspective, as they are paying a higher rate than is warranted given the riskiness of the loan. If the interest rate is equal to the discount rate (which is often assumed), then the NPV of the loan will always be zero, as you are paying a fair price for the money. Note that NPV zero implies that both the lender or the borrower are getting "fair deals."

Please send me excel sheet. ​@@docbrownexcels

You can download it at www.davidclaytonbrown.com/excel-resources

@@docbrownexcels thank you Brown, well explained, I've requested access as well for the file, appreciate if you could share with me.

Hello Andjela, Unique-Function is a Feature in Office-365. As Alternative it must be something more complex which I don't know. Good Luck finding the Work-Around!

Hi Precious, to avoid the penalties, hold down shift while hitting the right arrow 4 times. That will highlight 5 cells in a row. Then type 1 then Control + Enter. That will be 1s in all 5 cells. That builds the bridge and lets you move past that point. When you do the other SEQUENCE that goes down 6 cells, you can do the same thing, but I think you will need to type over the 1s with 2, 3, 4, 5 and 6. It'll slow you down a bit, but it should let you get through the exercise.

@@docbrownexcels Thank you 🙏

In your settings, there is an option to move the selected cell any direction when you press enter. You can uncheck that box so that you will not move in any direction. The setting is under home, options, advanced.

That setting is generally seen as an Excel modeling best practice.

@@docbrownexcels Thank you very much for your reply, it was really helpful. ✨👍

Hi Mustafa, which tab are you trying to build an all-in-formula for? In general, all-in-one formulas can be a fun challenge or can be useful sometimes, but I like to split calculations up to make them more transparent. It is also easier to catch errors that way.

@@docbrownexcels As I went further yep it's more clear to have the data in wider Form.. about what I meant like the total Cost of Rent where we can use Financial Formulas.. Thanks for Your Time Sir!

Thanks for the comment. I agree that there are more efficient ways to code this. I have found that the students do better with this conceptually because they have learned the OFFSET function earlier in class and can see it being implemented. If you have a preferred way to remove the Selection statements, I would love to see it.

Using vba is slowing your code down significantly

Both are actually very subjective. The cash flows come from assumptions built into a three-statement model which should be based on the business plan. At the end of the day, those assumptions are all just guesses (although hopefully educated guesses). The denominator, and the risks that should be incorporated into it, are also highly subjective as they depend on risk exposures (e.g., beta) and market factors (e.g., the market risk premium).

Hi Gigi - great, I am glad it was helpful. I used to teach Entrepreneurial Finance as well, but now focus on Financial Modeling. There are a lot of overlapping topics like this one.

Definitely, Im preparing right now for entreprenerial finance and am thankful for finding this video.