Hi, very interesting and thanks for the explanation. How do you incorporate a seed so that the results can be replicated by another person?
@docbrownexcels4 күн бұрын
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.
@FarhanaYeasmin-cg4zuАй бұрын
After using f9+1 and then Ctrl+D function, I went to 100 but no date pops up. Can you please guide me, Sir? Thanks
@NeerajJhawarАй бұрын
Jeez, you didn't even bother to edit the video a little? lol
@docbrownexcelsАй бұрын
Fair point - I missed cutting those first ten seconds - hopefully it didn't distract too much from the learning!
@NeerajJhawarАй бұрын
@@docbrownexcels It definitely does. Sets the tone initially so not a good idea.
@1VBURSEY4 ай бұрын
Dr. Brown thank you for your careful analyses reflected in the VC Method! Is the spreadsheet accessible? Thank you
@ninaisabell93044 ай бұрын
When I press enter on the documents cell after using sequence, no data pops up. Is there a different key I should press?
@eneazanellotti95746 ай бұрын
Is it avaible the excel ?
@edgars87699 ай бұрын
Nice video! 😎 Is there some addin needed? How to make the columns dynamic like that?
@docbrownexcels9 ай бұрын
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.
@edgars87699 ай бұрын
Thanks!@@docbrownexcels
@youngtrader26389 ай бұрын
I did this two different times and my FINISH button wont pop up at the end
@adeshshah2810 ай бұрын
very succinctly explained. thank you.
@user-hc8cr1vq9u10 ай бұрын
how would this work if you pay interest monthly and the principle is paid in full at maturity?
@docbrownexcels10 ай бұрын
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.
@user-fx9jd3hn7d11 ай бұрын
I couldn't do control shift 3 to format the date but control shift 4 formatting it into currency works. Any suggestions to solve the problem? Thanks.
@docbrownexcels11 ай бұрын
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.
@munozjuancarlos11 ай бұрын
great, thanks!
@whydidntwehomeschool11 ай бұрын
Downloaded today & when doing this challenge the macro is broken. When debugging, the isGameOn function is missing.
@docbrownexcels11 ай бұрын
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.
@restusatrio5408 Жыл бұрын
How do we calculate the discount rate?
@docbrownexcels Жыл бұрын
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.
@Garyket Жыл бұрын
After inputting the dynamic Interest rates and adjusting the formulas, why doesn't the Extra Payment feature work anymore?
@docbrownexcels Жыл бұрын
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.
@Garyket Жыл бұрын
@@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!
@Garyket Жыл бұрын
@@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!
@docbrownexcels Жыл бұрын
@@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.
@pradeepkandpal7523 Жыл бұрын
The CDF should've been cumulated to 1 and not 0.83.
@docbrownexcels Жыл бұрын
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.
@pradeepkandpal7523 Жыл бұрын
@@docbrownexcels. I just felt If am missing anything there. Glad you replied. Thanks for the explanation.
@pauldotto5130 Жыл бұрын
Thank you!
@hamzam9361 Жыл бұрын
Hello can you kindly share the excel files so I can see the formulas for reference?
@docbrownexcels Жыл бұрын
You can download it at www.davidclaytonbrown.com/excel-resources
@francoisesper570111 ай бұрын
@@docbrownexcels Could I please receive the Excel file? I've already submitted a request. Thank you.
@christinebeck8431 Жыл бұрын
Great series of videos! Should Cash Flows from Investing be subtracted from rather than added to the cash flow? See formula in J17.
@docbrownexcels Жыл бұрын
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.
@christinebeck8431 Жыл бұрын
@@docbrownexcels Ah, ok, thank you, that does clarify it. Thanks for the reply! A really excellent set of videos on financial statements.
@YESHUA_AKBAR_345 Жыл бұрын
nice video
@bmeizinger Жыл бұрын
In one video you have the discount rate column formula referencing the discount rate, in another video (pre-payments) I saw the formula is referencing the loan interest rate. Which should it be referencing?
@docbrownexcels Жыл бұрын
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."
@kamroonnahar23 күн бұрын
Please send me excel sheet. @@docbrownexcels
@vijayrao6321 Жыл бұрын
Your explanations are amazing! Thank you!
@ExcelCampus Жыл бұрын
I love this challenge, David! Very cool!! 👍
@sayednab Жыл бұрын
would you mind and share the excel sheet?
@docbrownexcels Жыл бұрын
You can download it at www.davidclaytonbrown.com/excel-resources
@xjtuxl Жыл бұрын
@@docbrownexcels thank you Brown, well explained, I've requested access as well for the file, appreciate if you could share with me.
@andjelakrstic4156 Жыл бұрын
Hi David! I don't have an unique function, may you suggest some alternative?
@Raph0ne Жыл бұрын
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!
@ThepExcel Жыл бұрын
Really Nice explanation, thx!
@preciousozegbe1024 Жыл бұрын
Hi David, I do not have the sequence function on my excel. Any suggestion on what I can do?
@docbrownexcels Жыл бұрын
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.
@preciousozegbe4108 Жыл бұрын
@@docbrownexcels Thank you 🙏
@marsaetos9610 Жыл бұрын
Good coverage of classic model in vba.
@gaolatlhemmelesi684 Жыл бұрын
Whenever I press 'ENTER' it automatically takes me off-track, how do I prevent this?
@docbrownexcels Жыл бұрын
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.
@docbrownexcels Жыл бұрын
That setting is generally seen as an Excel modeling best practice.
@gaolatlhemmelesi684 Жыл бұрын
@@docbrownexcels Thank you very much for your reply, it was really helpful. ✨👍
@Raph0ne Жыл бұрын
Hello, I'm challenging myself to build All-in-one Formula instead of spilling down the Values.. is it relevant? Or should we stick to the unofficial Table everytime?
@docbrownexcels Жыл бұрын
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.
@Raph0ne Жыл бұрын
@@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!
@sahilsah7731 Жыл бұрын
greattt
@michaelesan9399 Жыл бұрын
Insightful...
@lasseaberg5267 Жыл бұрын
I love it! 👌
@zveryok12 жыл бұрын
Nice application. Inefficient coding ability 🙂 all these selects are slowing down your code significantly.
@docbrownexcels2 жыл бұрын
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.
@rebinu10 ай бұрын
Using vba is slowing your code down significantly
@investwithvincent63292 жыл бұрын
so it sounds like the numerators (Cash Flows) are objective data, the denomiators (discount rates) are subjective
@docbrownexcels2 жыл бұрын
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).
@investwithvincent63292 жыл бұрын
Dope!
@investwithvincent63292 жыл бұрын
0:18 Outline 2:18 Required and surplus cash 3:58 Maximum Dividends 6:14 MDM income statment 6:44 MDM balance sheet 7:47 MDM cash flow statement 8:48 Value measures 10:14 Excel Spreadsheet example 11:36 Shortcomings of MDM
@AtarodoByOyin3 жыл бұрын
Can you please make the volume higher in your other videos? thank you !
@afeworktsigie7813 жыл бұрын
Thanks for the tutor and i am having difficulty on using monte carlo for production line optimization any idea.
@dwaynecampbell94533 жыл бұрын
Nice channel. About to subscribe. Why don’t you use Promosm!? It’s the best way to grow your channel.
@gigioh9213 жыл бұрын
I'm studying Entrepreneurial Finance and your videos are really helpful! Thank you
@docbrownexcels3 жыл бұрын
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.
@investwithvincent63292 жыл бұрын
Definitely, Im preparing right now for entreprenerial finance and am thankful for finding this video.