Greetings !! Today I'll be throwing light over some important applications of Excel's Solver Tool with respect to real life financial problems. Here, we will see that how this magical tool executes following "Monsters" before they can even make note of that.
*Pls Note: This Intel is devoted to Solver tool only, detailed explanation of various calculations(like NPV etc.) is beyond the scope of this Intel.
Monster 1 - Complex Mathematical Problem.
Suppose, mathematical formula given by
Y = R*(N+R)+N*(1+R/X)*(X+N/X)+(N/X)*(1+R/N)/(N+4)
Subject to constraints :-
X greater than or equal to 5
R = 15
N = (1+R/X) and N smaller than or equal to 1.5
We have to find the value of X for which Y is minimum.
Killing Monster 1 - Refer to Figure 1
Variables Y, X, R, N are entered in cells A1 to A4
Enter formula as shown in the figure in B1 to calculate Y
Enter any arbitrary values in cells B2 & B3
Enter formula =(1+B3/B2) in cell B4
Go To Tools - Solver, It will open solver window as shown, Enter Solver parameters as shown in the figure and click Solve.
We will find that for value X = 30, Y is minimum i.e. 315
------
------
Monster 2 - Cost of Capital (Cost of Debt Borrowing)
Simplest Formula for calculating Pre-Tax Cost of Debt is as Shown in Figure 2, Kd represents the pre-tax cost of Debt Borrowing.
Now I want to know minimum rate of return that Firm must earn from the raised capital through debt/bond whose current value is 1200 Euro, Maturity Value 2000 Euro, Annual Coupon rate is 10% and maturity period is 10 years.
Killing Monster 2 - Refer again to Figure 2
We can see the formula entered in cell B1, Enter solver parameter as shown and click solve.
We will find that, Firm must earn atleast 14% (i.e after-tax cost of debt) to maintain its value.
------
------
Monster 3 - Capital Budgeting (Buy Vs Leasing)
Lessee has 2 Options, either to purchase an equipment or lease it. (Terms of above options are as given in the figure 3). Now it is required for lessor to find the maximum lease rate that will be accepted by Lessee.
Killing Monster 3- Refer to Figure 3
Firstly it is required to calculate the Net present Value (NPV) of cashflows from the two options. Lessee will accept any lease rate which is less than or equal to the rate at which NPV from both the options is equal.
So from figure, by using help of Solver Tool, we can find that Lessee will accept lease rate smaller than or equal to 10%, because at this lease rate, NPV from both options will be equal, so he/she will be interested in opting Lease option, in which he/she has to pay equal or less lease interest payments than 10%.
------
------
List is very long, this can go on and on....
I think these three examples have given a good idea about Excel Solver tool.
See ya.... Don't forget to comment... Cheers !!
