What IF Analysis – Goal Seek

Advanced Excel Crash Course Section 14: What-If Analysis
4 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.78
List Price:  €129.90
You save:  €37.11
£79.40
List Price:  £111.16
You save:  £31.76
CA$136.11
List Price:  CA$190.56
You save:  CA$54.44
A$154.13
List Price:  A$215.78
You save:  A$61.65
S$135.08
List Price:  S$189.12
You save:  S$54.03
HK$782.28
List Price:  HK$1,095.23
You save:  HK$312.94
CHF 90.61
List Price:  CHF 126.85
You save:  CHF 36.24
NOK kr1,085.23
List Price:  NOK kr1,519.37
You save:  NOK kr434.13
DKK kr692.01
List Price:  DKK kr968.84
You save:  DKK kr276.83
NZ$167.80
List Price:  NZ$234.94
You save:  NZ$67.13
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳10,976.08
List Price:  ৳15,366.96
You save:  ৳4,390.87
₹8,339.52
List Price:  ₹11,675.66
You save:  ₹3,336.14
RM473.25
List Price:  RM662.57
You save:  RM189.32
₦141,842.81
List Price:  ₦198,585.61
You save:  ₦56,742.80
₨27,810.04
List Price:  ₨38,935.18
You save:  ₨11,125.13
฿3,647.70
List Price:  ฿5,106.92
You save:  ฿1,459.22
₺3,232.12
List Price:  ₺4,525.11
You save:  ₺1,292.98
B$499.21
List Price:  B$698.91
You save:  B$199.70
R1,908.54
List Price:  R2,672.04
You save:  R763.49
Лв180.65
List Price:  Лв252.92
You save:  Лв72.26
₩135,197.71
List Price:  ₩189,282.20
You save:  ₩54,084.49
₪368.63
List Price:  ₪516.10
You save:  ₪147.47
₱5,633.91
List Price:  ₱7,887.71
You save:  ₱2,253.79
¥15,144.47
List Price:  ¥21,202.86
You save:  ¥6,058.39
MX$1,659.40
List Price:  MX$2,323.22
You save:  MX$663.82
QR364.31
List Price:  QR510.04
You save:  QR145.73
P1,370.91
List Price:  P1,919.33
You save:  P548.42
KSh13,148.68
List Price:  KSh18,408.68
You save:  KSh5,260
E£4,729.52
List Price:  E£6,621.52
You save:  E£1,892
ብር5,680.63
List Price:  ብር7,953.11
You save:  ብር2,272.48
Kz83,612.74
List Price:  Kz117,061.18
You save:  Kz33,448.44
CLP$97,978.20
List Price:  CLP$137,173.40
You save:  CLP$39,195.20
CN¥722.95
List Price:  CN¥1,012.16
You save:  CN¥289.21
RD$5,921.50
List Price:  RD$8,290.34
You save:  RD$2,368.83
DA13,490.83
List Price:  DA18,887.70
You save:  DA5,396.87
FJ$226.12
List Price:  FJ$316.58
You save:  FJ$90.46
Q779.86
List Price:  Q1,091.83
You save:  Q311.97
GY$20,923.51
List Price:  GY$29,293.76
You save:  GY$8,370.24
ISK kr13,946.60
List Price:  ISK kr19,525.80
You save:  ISK kr5,579.20
DH1,013.19
List Price:  DH1,418.51
You save:  DH405.32
L1,763.34
List Price:  L2,468.75
You save:  L705.40
ден5,702.11
List Price:  ден7,983.18
You save:  ден2,281.07
MOP$805.89
List Price:  MOP$1,128.28
You save:  MOP$322.39
N$1,893.44
List Price:  N$2,650.90
You save:  N$757.45
C$3,681.15
List Price:  C$5,153.75
You save:  C$1,472.60
रु13,335.63
List Price:  रु18,670.42
You save:  रु5,334.78
S/370.84
List Price:  S/519.19
You save:  S/148.35
K382.72
List Price:  K535.82
You save:  K153.10
SAR375
List Price:  SAR525.01
You save:  SAR150.01
ZK2,522.76
List Price:  ZK3,531.96
You save:  ZK1,009.20
L461.43
List Price:  L646.02
You save:  L184.59
Kč2,350.75
List Price:  Kč3,291.15
You save:  Kč940.39
Ft36,729.02
List Price:  Ft51,422.10
You save:  Ft14,693.08
SEK kr1,071.30
List Price:  SEK kr1,499.86
You save:  SEK kr428.56
ARS$85,766.82
List Price:  ARS$120,076.98
You save:  ARS$34,310.16
Bs691.04
List Price:  Bs967.48
You save:  Bs276.44
COP$387,583.68
List Price:  COP$542,632.66
You save:  COP$155,048.97
₡50,832.34
List Price:  ₡71,167.31
You save:  ₡20,334.97
L2,468.78
List Price:  L3,456.40
You save:  L987.61
₲737,805.73
List Price:  ₲1,032,957.54
You save:  ₲295,151.80
$U3,781.90
List Price:  $U5,294.82
You save:  $U1,512.91
zł400.73
List Price:  zł561.05
You save:  zł160.31
Already have an account? Log In

Transcript

Greetings everyone. In this video session we'll talk about two components of Excel. One is PMT formula, which is often used to find the easy monthly installments for any loan that you have taken in the past or wish to take in the future. And go see how do you back calculate certain desired numbers. So first, let me look at the various variables which are going to go into the ingredients of this recipe. The first thing I have learned amount, interest percentage and the duration that means, in two years flat, I'm going to pay these loan back fully.

And this one variable which is going to be a part of the formula which says if you want to pay towards the end of the month, then the input should be zero equals one. Now, let me write the PMT formula equals PMT. I go to the FX button Insert Function click on it. Now notice I am writing equal PMT and then opening the brackets and then going to FX button if you had not opened the brackets or parenthesis If you then go to FX, it will not lead you directly to the PMT formula. So equal to PMT pressing TAB key clicking on FX button now rate choosing the cell cutting the percentage annual interest percentage divided by 12 thereafter number of periods which indicates the duration and this to make it consistent with the formula inputs I'm going to multiply with 12 you are paying monthly installments present value I am getting a loan worth of five lakhs today's term and that's the present value.

There are two other optional conditions, which if I won't I can ignore for the time being. So I press OK. Oh, I get an amount of credit 3007 71 that is the monthly check amount that you need to deposit towards the bank loan repayment. Now, let's for a moment assume that you have another loan in the backdrop and you only have 20,000 per month as the school residual income, which you can use this to service the debt. Now, how do I know that? How do I increase the duration to let's say 2.5 years or 2.6 years or three years? What is the exact number of years at which point the exact Am I going to be 23 when he tells him so let's find out, I keep my cursor on the cell, the cell which contains the amount of EMI, I then go to data.

What if analysis and let me click on Goal Seek, I click on Goal Seek. So, I see set this cell, the cell containing the calculation for the PMT, to what value now notice it doesn't say cell which means you cannot choose another cell. It will give you an error. It has to be a value which needs to be hard coded. So I wish to write minus 20,000. Why so because this is an outflow, this is a cash outflow which is going out of pocket, hence my It's pretty tough to be careful about that.

Set the cell because one containing PMT formula to a value of negative 20,000 by changing cell by changing. Let's see amount. Let's see what comes up next. As I press OK. It says it has back calculated Goal Seek has found the solution. Do you want to keep the solution I say okay.

So basically, if you have 20,000 disposable income for the loan repayment and you wish to pay this back in two and a half years, then this is the amount of loan that you can avail against these money variables. On the other hand, let's say I want to take only five lakh worth of loan, I press Ctrl Z to bring it back. Now I want to see exactly in how many years can I repay the loan back in if 20,000 is the amount I can pay towards the loan repayment. So I go back to go seek again. I say set this cell to negative 20,000. If you write a positive 20,000 it will go in in an indefinite loop, which you will not want.

So, you go ahead by saying by changing cell and this time we take the cell containing the duration, okay? it back calculates, and it tells you exactly 2.44 years is when you'll be able to repay the loan back if you are paying getting the installment of 20,000 each every month. So that was go seek that was PMT formula, often used for that calculation.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.