Multimedia-Introduction-to-Business-Analytics-with-Excel-1_-Ren_-Justin_-Z-Business-Analytics-with-E

["","Chapter 4. Basic Financial Functions In this Chapter (1) NPV: Net Present Value (2) PMT: payment (3) IRR: Internal Rate of Return (4) FV: Future Value (5) RATE (6) NPER","(1) NPV: Net Present Value Net present value calculation is one of the most frequently used financial function. Excel makes such calculation extremely simple. Example (Buying an apartment) Thomas is considering buying an apartment, renovating it, and then leasing out to tenants. The buy price for the apartment is $425,000. He can then immediately rent it out for a annual income of $30,000. Thomas plans to keep the apartment for 7 years, by the end of which he estimates that he could sell the apartment for $470,000. The discount rate he uses is 5%. Calculate the NPV of this project once he buys the apartment. For simplicity, assume that all transactions happen at the end of each year. goo.gl\/2CUwgs goo.gl\/BNdBQK Solution In order to calculate present values, we need two things: (1) discount rate; and (2) stream of cash flows. With those, the Excel function","calculates the present value as follows: NPV(discount rate, cash flow) In the example here, the cash inflows are recorded in B5:B12. Because the initial investment is made at time 0, we subtract that after we calculate the present value of cash flows. So in cell A14, we enter =NPV(B3,B5:B12)+B4 So Thomas can expect to earn a profit of $66,705.70 from this project. Note: Excel\u2019s NPV assumes that cash flows occur at regular intervals. If cash flows does not happen at regular intervals, one can use another Excel function called XNPV. Quick Summary: NPV(discount rate, cash flow 1, cash flow 2, ...) Report Issues (goo.gl\/y3kJmv)","","(2) PMT: Payment Whether a car loan payment, or mortgage payment, PMT can easily compute those for you. Next, we illustrate the usage of PMT function with a car payment example. Example (Choose a loan) Emily is in the process of purchasing a new car. She has found the car model she wanted from a dealer and obtained a final price quote of $34,500, which she accepted. The dealer offers two financing options: zero down with annual interest rate of 0.99%, or $5,000 down payment with annual interest rate of 1.25%. Both are to be paid at the end of each month over 5 years (i.e. 60 months). Emily is interested in paying the least amount of interests. Use Excel to calculate which financing option is best for Emily. goo.gl\/HfWLvS goo.gl\/rcikfq Solution To figure out the total interest amount from a loan, we use the following relationship:","Total Interest of Loan = Total Loan Payment - Principal Amount and Total Loan Payment = Number of Payment \u00d7 Each Payment Amount So the key here is to calculate monthly payment for each loan. Excel\u2019s PMT function has a number of parameters, some of which are optional (in gray) PMT(rate of return, number of payments, present value, [future value],[0-due at end of period, 1- due at beginning of period]) In the example here, to compute the monthly payment for loan 2 for example, we type the following command in cell C10: =PMT(C6\/12,C7,C5) Note that the interest rate in C6 is divided by 12 to reflect the fact that payments are made in monthly installments. Since we assume the car is fully paid for by the end of the loan period, there is no future value that needed to be entered in the formula. Also since payments are due at the end of each month, we can leave that option out because it is the default. Once we figure out monthly payments for the two loans, multiplying them with 60, the number of payments, gives us the total payment for the loans. As we expect, the total payment greater than the original principal amount because of all the interests that have to be paid. In fact, the difference between the two is exactly the amount of total interests, which we compute in B12 and C12. From those, we can see that loan 1 has a lower amount of total interests compared to loan 2. Hence Emily should pick loan 1. Quick Summary: PMT(rate of return, number of payments, present value, [future value],[0-due at end of","period, 1- due at beginning of period]) Report Issues (goo.gl\/y3kJmv)","","(3) IRR: Internal Rate of Return In making investment decisions, managers often have to ask the following question: At what return rate will I break even? This is exact what IRR can answer: the rate of return at which the net present value of a project is zero. Example (IRR for a real estate project) In the earlier example of \u201cBuying an apartment\u201d, Thomas has projected the entire cash flow of the project. Calculate its internal rate of return. goo.gl\/QddO4U goo.gl\/R93aiA Solution To calculate IRR, all it needs is a series of cash flow, and an initial guess (you can enter whatever you think is close to the real number; it does not really matter). There has to be one negative cash flow representing initial investment, otherwise the IRR will be infinity!","To calculate the IRR of this project, simply enter in cell B13: =IRR(B3:B11,10%) Here 10% is our guess. We get our answer of 6.94%. This tells us that based on this set of numbers, this project will break even at 6.94%. So if Thomas\u2019 cost of capital is lower than that, he could make money and be profitable; Otherwise, future cash inflow will be discounted too much for the whole project to be profitable. Quick Summary: IRR(cash flow , guess)","","(4) FV: Future Value FV computes the value, at a future date, of a series of payments made before the future date. When you want to calculate the value of an investment that will pay off in the future, this is the function to use. Example (Retirement Portfolio, Part I) Jack and Mary are planning to retire in 20 years. They plan to invest in a mutual fund that yields a 5% annual return on the average. Every month, they plan to put in $1,200 in this fund at the beginning of each month, and they would like to start with an initial investment of $5,000. When they retire in 20 years, how much is their investment worth? goo.gl\/4WnPIi goo.gl\/rDJYfx Solution FV function has the following structure: FV(interest rate per period, number of payments, payment per period, [present value],[0-due at end of period, 1- due at beginning of period]) So to calculate Jack and Mary\u2019s portfolio, we plug in all the given numbers in cell B8:","=FV(B3\/12,B4,B5,B6,1). Excel\u2019s answer: $508,858.77. So if Jack and Mary keeps on contributing to their retirement account for 20 years, they will be rewarded with over half a million dollars! FV function has many other uses as well. For example, it can be used to calculate compounded interest from savings. Example (Calculate interest payment) Jency made a deposit of $900 into a savings account exactly 13 months ago. The interest rate is 1%. What is the interest amount that she will get if she withdraws the entire amount? Solution In this application, there is no change to the principal amount (i.e., no payment or additional deposit) during the savings period, so we can leave that term empty, or fill in a zero. We still have the present value (i.e., the original deposit we start with in cell B3), the number of periods (cell B5). The formula for the total amount is thus: =FV(B4\/12, B5,0,-B3,1)","goo.gl\/VlqloY goo.gl\/FiHUw9 Note that we interest rate we use in the formula is the interest rate per compound period, which is in months. Of course, you can use the direct compounding formula to double-check: = 900 * (1 + 1%\/12)^13 = 909.80. Quick Summary: FV(interest rate per period, number of payments, payment per period, [present value], [0-due at end of period, 1- due at beginning of period]) Report Issues (goo.gl\/y3kJmv)","","(5) RATE RATE is the \u2018cousin\u2019 of PMT and FV. Instead of calculating each payment for a given payoff structure, RATE calculates the rate of return required to meet a certain inflow and outflow structure. Example (Retirement Portfolio, Part II) In the previous example of calculating retirement worth, we calculated Jack and Mary\u2019s portfolio worth ($508,858.77), assuming a fixed return rate (5%) and contribution pattern ($1,200 per month for 20 years, $5,000 to begin with). Now, let\u2019s flip that problem around and ask the following: Suppose Jack and Mary is shooting for a total worth of $508,858.77 with the same investment structure --$1,200 per month for 20 years, $5,000 to begin with -- What is the rate of return that would allow them to meet the goal? Solution From the previous retirement portfolio example, we know the answer should be 5%. But can we back that out using the RATE function? goo.gl\/lfKsiS goo.gl\/d4fNAe We know our targeted future value which we put in cell B3. The investment structure remains the same: 240 month (cell B4), present value of $5,000","(cell B5), and monthly payment of $1,200 (cell B6). The RATE formula has the following inputs: RATE(number of payments, payment per period, present value, future value, [0-due at end of period, 1- due at beginning of period], [guess]) Future value is an optional term, and is assumed to be zero if left out. But of course, future value has a specific amount, which is the targeted portfolio value. There is one more optional term at the end, where you can input your \u201cguess\u201d of the answer. If left empty, Excel will take an arbitrary number (10%) as a starting point, and does its computation from there. For all practical purposes, there is no really need to worry about what a good guess will be. It is safe to ignore it. So we enter the following: =RATE(B4,B6,B5,B3,1) The answer is cell B8: 0.42%. Note that this is the monthly rate, because all the calculations are done in months. To convert the monthly rate into annual year, simply multiply it by 12 and we get: 5%, thus confirming that the RATE function has a direct one-to-one relationship with the FV function. Quick Summary: RATE(number of payments, payment per period, present value, future value, [0-due at end of period, 1- due at beginning of period], [guess]) Report Issues (goo.gl\/y3kJmv)","","(6) NPER NPER can be viewed a cousin of the PMT and RATE functions. Given a specific payment schedule with a set of present value, future value, and interest rate, the NPER returns the number of periodic payments that are needed. Example (Retirement Portfolio, Part III) We again use the previous example of Jack and Mary\u2019s portfolio. Now suppose we know the annual rate of return of 5% with the same fixed payment schedule. In order to reach the target value of $508,858.77, how many contributions do Jack and Mary need to make? goo.gl\/peC2kR goo.gl\/m8YG9b Solution As usual, we list the needed inputs first: the targeted portfolio value (cell B3), present value (cell B4), payment (cell B5), and rate of return (cell B6). The rule for this function is:","NPER(interest rate per period, payment per period, present value, [future value],[0-due at end of period, 1- due at beginning of period]) Here future value is optional; If omitted, it will be assumed at zero. So plugging in all the numbers, we enter =NPER(B6\/12, B5, B4, B3,1) Again, note that we need to convert the annual rate into monthly one. Excel produces the answer of 240, which we know is correct from the previous examples. Quick Summary: NPER(interest rate per period, payment per period, present value, [future value],[0-due at end of period, 1- due at beginning of period]) Report Issues (goo.gl\/y3kJmv)","","","Chapter 5. Text Operations In addition to its power in processing numbers, Excel has strong capabilities in working with texts. Even though we may not extensively work with texts, it is helpful to know some of Excel\u2019s functionalities in this area. We demonstrate that with an example of working with a zip code. Example (Working with Zipcodes) A zip code 01778 is entered as a text in cell B3 (To enter a number as a text, when typing in the content add an apostrophe in the front: \u201801778). Perform the following operations with this string of text: 1. Take the first three digits 2. Take the last two digits 3. Put the previous two together 4. Convert the resulting text to number 5. Convert the resulting number to text 6. Add a \\\"0\\\" in the front of the text 7. Find the first \\\"1\\\" in the zip code 8. From that first \\\"1\\\", take the next 3 characters","goo.gl\/ZyG7tv goo.gl\/ij2dC7 Solution Our first task is to take the first three digits of the zip code. This is done by the LEFT function. In cell B5, enter =LEFT(B3,3). This will take the 3 left-most characters out of the text string. Second, we want to take the last two digit of the zip code. This is done by the RIGHT function. In cell B6, enter =RIGHT(B3,2). This will take the 2 right-most characters out of the text string. Tip: Numbers or texts? How can one know if a number is entered as a text in Excel, but not a number? A simple","sign is that numbers in Excel are right-aligned by default, and texts are left-aligned by default. Thirdly, we want to put the previous two pieces together, and restore the original zip code. This is called `concatenating'. In Excel, it is handled rather simply with the special symbol &. In cell B7, just enter =C5&C6. Similarly you can attach other texts to it. For example, if you want to prefix the word `zip code' to it, simply enter =\\\"zip code \\\"&C5&C6. Now that we have the zip code as texts, how to let Excel treat it as a number? Use the VALUE function. In cell B8, enter =VALUE(B3). Excel will convert it to a number. Notice that Excel now drops the preceding `0' because now the whole string is a number. A quick trick to convert text to number is to simply multiply the text with 1, as is done in cell B10. The formula there is =B3*1. How to convert number back to text? Use the TEXT function. In cell B11, enter =TEXT(B10,``0\\\"). The second argument in the function ``0\\\" is telling Excel to use the specified no decimal-point format. If you want, say, the two decimal point format, you may enter =TEXT(B10,``0.00\\\"). Lastly, we illustrate the use of FIND and MID. In cell B13, we basically add a \u201c0\u201d to restore the full 5-digit zipcode as a text. Suppose you want to find the fist ``1\\\" in the zip code. To do that, enter the following in cell B14: =FIND(``1\\\", B13). Excel will return the position of the first ``1\\\" in the string ``01173\\\". Here, it will return a 2. After you find the position of the first ``1\\\", suppose you want to take the next 3 characters, including the first ``1\\\". Here we use MID function. Enter, in cell B15, the following formula: =MID(B3,B14,3). It will take the zip code in cell B3, find the first ``1\\\", whose position (#2) is stored in cell B14 , and take the","next three characters including the one in position 2. What you get is the string ``117\\\". There are many other text functions in Excel. To explore more, go to Formula \u2192 Text. You will be shown a long list of useful functions here (see below): BAHTTEXT, CHAR, CLEAN, CODE... You are encouraged to explore on your own -- you may be surprised to find out what they can do! Report Issues (goo.gl\/y3kJmv)","","","READY FOR MORE? Now that you have equipped yourself with the essential Excel analytics skills, are you ready to apply them in building some Excel models to solve real- world problems? If so, take the next part of the series: Build Models!","",""]