Excel with scenarios - Angular | MSBI | Design Pattern

[Pages:37]EXCEL Real time problems and solution

(Covering real time financial, accounting and day to day office problems)

Shivprasad Koirala Sham Sheikh

Visit us at for more details.

This book is a sample book. For buying a hard copy please contact shiv_koirala@ or call at 9967590707. We conduct EXCLUSIVE excel classes in Mumbai for working class people. After the course you can solve your day to day cryptic office issues with ease.

Please call 9967590707 for more details.

Common Formulas....................................................................................................................... 3 How to find average of a given set of results?......................................................................... 3 What is the use of Goal seek command? ................................................................................. 3 How do we concatenate in excel?............................................................................................ 4

Investment, loan and Interest calculation..................................................................................... 5 How can we calculate compounding interest in excel? ........................................................... 5 How do we calculate future and present value for series of cash flow? .................................. 7 What are data tables and how can it helps us?......................................................................... 9 To achieve 61051 INR after 5 yrs at 10% interest rate what should be the yearly investment amount?.................................................................................................................................. 18 How do we calculate number of years for given rate of interest and loan value? ................. 19 How do we calculate rate of interest? .................................................................................... 20 You have two models of investments you want to know which is the best?......................... 21 How can we compare a business plan investment with fixed rate returns?........................... 22 How do we calculate a value of a bond using excel? ............................................................ 23 How can we calculate Current yield, YTM and YTC for bonds? ......................................... 26 You have two stocks how will you decide the investment strategy for the same?................ 28 You have three stocks you want to choose two stocks with diversified investment?............ 30

Deprecation ................................................................................................................................ 31 How do we calculate straight line depreciation? ................................................................... 31 What is double declining depreciation?................................................................................. 32 How do we calculate depreciation using `sum of year digit'?............................................... 34 How can we calculate depreciation for a certain time period? .............................................. 35

Common Formulas

How to find average of a given set of results?

Many times you get data as shown in figure `Average in action'. In the below scenario we have number of items sold for every month. We need to find average of sales across all these months. Figure `Average in action' is also numbered. So let's try to understand the same according to numbers. 1 and 2? This represents the months and corresponding sales to those months. 3 ? We have used the formulae `Average' and selected the items sold every month. In the right hand side of the same figure we can see the final average value. We have sold approximately 274 items on an average every month.

Figure: - Average in Action

What is the use of Goal seek command?

In normal scenarios excel takes input and gives out some output. Sometimes situation demands that depending on output we need to find what the input is. Let's try to understand what does this mean. Consider the below situation outlined in figure `problem' below. In the table we have sales percentage for every year . At the end of the row we have calculated the average of the percentage profit. This average is calculated for four years. Now we need to find out to achieve a profit percentage of 35 how much sales should do at the fifth year. So now we need to calculate input by which we can achieve an output (35 % average).

Figure: - Problem We can calculate the same by using `goal seek' command. So click on tools? goal seek and you should be popped with a goal seek dialog box shown below. There are three values first is the `set cell'. `set cell' column is nothing but the output cell so in this case it's the cell which displays average value (B7). Second column is the `to value'. This column specifies what is the output targeted. As said previously we are targeting average of 35% so we specify the value 35. The third column `By Changing Cell' is the input which needs to be calculated for the targeted output. In this case it's the fifth year column. Once we specify all the three columns we are all set to get the input calculated for the target output. Click ok and you should see the auto adjusted value for input (fifth year sales percentage) and output (average) as shown in figure `Goal Seek in Action'.

Figure: - Goal seek in action

How do we concatenate in excel?

Concatenation is one of the common problems which occur in excel documentation. For instance below figure `Concatenation in action' shows we need to concatenate the telephone number with code. While doing the concatenation we also need to separate the telephone number and area code with hyphen ("-"). This can be easily achieved by using the

`Concatenate' keyword. `Concatenate' keyword takes series of string value and displays the same in a concatenated fashion. You can see from the figure the first is the area code, second input is the hyphen "-"and the final is the telephone number. We have also shown the actual output for the same in the same figure.

Figure: - Concatenation in action

Investment, loan and Interest calculation

How can we calculate compounding interest in excel?

Before we get in to how to calculate compound interest in excel. We need to understand three important concepts present value (PV), future value (FV) and compounding. Let's say you have 2000 INR and you give it to a bank for fixed deposit with rate of interest of 10%. So this 2000 INR is your present value. Now after one year with 10 % interest you will get 2200. So 2200 is your future value. Now lets say you again put 2200 for the next year with 10% rate of interest you will get 2420, this is termed as compounding. Below figure `Compounding in action' shows how the compounding fundamentals work. In the same figure we have also give the formulae which is used to calculate compounding interest.

Figure: - Compounding in action

Excel provides `FV' formula to calculate compounding interest. Using formula `FV' the compounding interest calculation becomes a cool breeze. Below figure `FV in action' shows how it can be used. It has five parameters. `PMT' and `Type' is zero we will get in to details of both these parameters in the coming questions. `Rate' is the rate of interest, `nper' is the number of years for which interest has to be calculated and `pv' is the present value. So with `present value' equal to 2000, interest rate of 10% for two years gives us 2420 by applying compounding formula.

Figure: - FV in action Now let's give a simple twist to the above example, what if we want to calculate present value depending on future value and rate of interest. So let's consider the same example. Below figure `PV in action' shows how we can do the reverse calculation if we are given the future value and interest rate.

Figure: - PV in action

Note: - Type parameter tells at which preiod the transaction occurs. If it happens at the start of period ( i.e. at 1st Jan of the year) then type = 1 and if it occurs at the end of the year ( i.e. 31st December) then type =0.

How do we calculate future and present value for series of cash flow?

In the previous question we had only a single cash flow. In other terms we had a present value (2000) which was then carried further year by year with a specific rate of interest (10 %). Now let's take a scenario where you need to pay cash every month with a specific rate of interest. So you have series of cash flows and you need to calculate the present value and future value. So let's consider the below scenario of retirement planning. During the retirement stage we expect to withdraw every month 10,000 INR. We have identified a bank which pays 10% interest so how much should you invest so that you can achieve your monthly target.

Figure: - Cash flow

In this case we need to calculate the present value we need to invest so that we can get 10,000 INR every month. In the previous question we have already used PV formula. We need to use the same but with a bit twist as the scenario is of a series of cash flow and not a single cash flow. Below figure `PV for series of cash flow' shows how we should use PV for series of cash flow. For series of cash flow we need to specify the `pmt' value and give `fv' value as zero. In this case we have assumed that the transaction will occur at the end of period so we have give type=0. So rate is nothing but the interest rate , `nper' is the number of years, pmt is the expected withdrawal per month , fv is zero as it's a single cash flow and type =0. In the same figure we have also given the output. So by investing today 37907 INR we should be able to withdraw 10,000 INR every year for the coming five years.

Figure: - PV for series of cash flow Now let's understand the same other way around. You are investing 10,000 every month, with 10% rate of interest, so how much should you get?. Below figure `Future value' shows the same in a pictorial format. If you look at the problem its nothing we need to find the future value for series of cash flow.

Figure: - Future value To find future value we need to use the `FV' formula. Below is a full snapshot which shows how we can use the formula. We have specified rate of interest, period and `pmt' value. As this is a series of cash flow we have specified `pv' and `type' as zero. So if we invest for 5 years with 10,000 INR every year with 10% rate of interest we will get 61051 INR.

Figure: - Future value in action

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download