ADVANCED EXCEL TRAINING
MODULE 4
USING THE PAYMENT FUNCTIONS (PMT)
• The Excel PMT function is a financial function that calculates the
payment for a loan based on a constant interest rate, the number of
periods and the loan amount. "PMT" stands for "payment", hence the
function's name. It includes Rate, Nper, PV, FV and Type
• The Rate is the interest rate per period for the loan.
• Nper is the total number of payments for the loan.
• PV is the present value. That’s the total number of payments that a
series of future payments is worth now
• FV is the future value or cash balance you want to attain after the last
payment is made.
• Type is a logical value: i.e. payment at the beginning of the period and
payment at the end of the period = 0 or omitted
How to use the PMT Function
• To perform financial calculations using the PMT function:
• Click Formulas
• Click Financial
• Click PMT
• On the PMT dialog box, under rate, select the specified interest
rate then divide it by 12 (based on annual interest rates)
• Under Nper, click on the loan payment period (usually in months)
• Under pv click on the loan amount
• Leave the fv and type field blank (except when you're calculating
future investments that’s when you make use of the fv field )
• Click ok
WHAT IF ANALYSIS
• It is used to see the impact of different scenarios, both positive and
negative, on the outcomes. However, the primary purpose of a what-if
analysis is to provide valuable insight to make more informed business
decisions. What-if analysis helps businesses to make strategies to counter
potential bottlenecks in the future. The What IF Scenarios include: Goal
Seek, Data Table, Scenario Manager and Solver
• Goal Seek: Goal seek is used to solve what if problems by finding the right
inputs for the value you want for example you can use Goal Seek to
determine price of car you can afford based on trade in value of the old car
and down payment.
• To perform Goal seek: click on the formula cell, click Data click what if
analysis, the set cell is already highlighted, under to value enter a new
value, under by changing cells, click either the down payment or trade in
value of the old car to calculate the price of car you can afford
What IF Analysis – Data Table
• Data Table: Data Table is used to solve what if problems by
seeing the results of multiple inputs at the same time. They are
two types of data table: one variable data table and two variable
data table. One variable data table looks at only one variable to
solve the what if problem e.g. interest rate only or down payment
only while two variable data table looks at two variables to solve
e.g. both the interest rate and the down payment.
• To solve a what if problem using data table: For example you can
use data table to perform a car loan analysis based on constant
interest rate, loan amount and duration in order to determine
future interest rate and future down payments:
What IF Analysis – Data Table
• For one variable data table, highlight the monthly mortgage
payment formula result and the new down payments only
• Click Data, Click What If Analysis, Click Data Table, under
row input cell, click the existing interest rate, under column
input cell, click the existing down payment
• Click ok to calculate the new down payments
• For two variable data table, highlight the monthly mortgage
payment formula result and both the new interest rates and
the new down payments
• Click Data, Click What If Analysis, Click Data Table, under
row input cell, click the existing interest rate, under column
input cell, click the existing down payment
• Click ok to calculate both the future down payments and the
future interest rates.
What IF Analysis – Scenario Manager
• Scenario Manager: Scenario Manager is a What If
Analysis tool which is used to create different groups of
scenarios and switch between them. Its also used for
predictions and forecasting models by using the
current value to determine a futuristics value.
• With scenario manager you can create different
scenarios and use those scenarios to compare the
futuristics values based on the current values.
What IF Analysis – Scenario Manager
• For example if you have expenditures for rent, energy, gas, etc that
includes cost for this year and expected % increase. You can use these
values to calculate expected cost for next year by multiplying the cost for
this year by the expected % increase. Then from the resulting outcome, you
can then create two scenarios a Best Case Scenario and a Worst Case
Scenario where you can then define new expected % increase for both
scenarios then finally compare the new results for both Scenarios with the
current values.
• At the end of the day when you compare the results of both scenarios, the
resulting outcome will indicate that for best scenario, the overall expected
cost for next year will be lower than the current values while for the worst
case scenario, the overall expected cost for next year will be higher than
the current values respectively
What IF Analysis – Scenario Manager
• Then from the resulting outcome, you can then create two scenarios a
Best Case Scenario and a Worst Case Scenario where you can then define
new expected % increase for both scenarios then finally compare the new
results for both Scenarios with the current values.
• Steps:
• To determine the expected cost for next year, Multiply the cost for this
year by the expected % increase using the product function and then add
the cost for this year e.g. = PRODUCT( B39:C39)+B39 where B39 is the
cost for this year and C39 is the expected % increase respectively then
sum all of them to get the total
• Click on the Total Expected Cost for Next Year
• Click Data, Click What If Analysis, Click Scenario Manager
• Enter the Scenario Name e.g Best Case, changing cells highlight all the %
increase
What IF Analysis – Scenario Manager
• Click Ok then Excel will prompt you for new expected % increase values
• Enter the new expected % increase values…Note that for best case the
% increase values will be lower than the current values
• Click Ok when you're done
• Repeat the same steps for worst case click Add
• Enter the Scenario Name e.g worst Case
• Under changing cells highlight all the % increase
• Click Ok then Excel will prompt you for new expected % increase values
• Enter the new expected % increase values…Note that for best case the
% increase values will be lower than the current values
• Click Ok when you're done
• Click Summary to see the scenario results for both scenarios
What IF Analysis – Solver
• Solver is a what IF Analysis tool that finds the optimal value of a target
cell by changing the values in cells used to calculate the target cell.
• The Solver uses three analytical tools to perform the What IF Analysis
which include Max, Min and Value of
• For Max, values must be at least the specified changing cell values
• For Min, values must be at most the specified changing cell values
• While Value of indicates exact value used to determine the target cell
• For e.g. you want to purchase three sets of cars Honda, Camry and
Toyota Corolla at one car each based on their current prices, you can
use Solver to determine how many cars you can buy at the current
price based on the cash you have at hand and also how much you
need to purchase certain number of cars you want to buy if the
number of cars changes
What IF Analysis – Solver
• Steps (Using value of ) Based on the cash you have at hand
• Click on the Total cost of buying the three cars
• Click Data, Click Solver
• Click Value of, Enter the Cash you have at hand to purchase those
cars
• By Changing Cells, highlight the number of cars for the three cars
• Click Add to Add the Constraints
• Add constraints for each car under cell reference click each car no.
e.g. 1 then select int to add the integer constraint) (Use int to Add
the integer constraint so the results will be whole number)
• Click Add again, Repeat same process above for the other 2 cars
click ok
• Click Solve for the Solver to Solve the What IF Analysis
Using the 3D Formula Referencing
• The 3D Formula Referencing is used in Excel to reference data from
different worksheets using the reference worksheet formula also
known as the 3D Formula
• For example you can use the 3D formula reference to reference the
calories of respective ages while the ages are on the current
worksheet the calorie values are stored in another worksheet being
the calorie worksheet.
• For example to calculate the calories based on ages click on the age
cell on the current worksheet then move to the calorie worksheet
and click to generate the 3D formula then click the cell reference
where the calorie value is stored in the other worksheet.
• E.g. of 3D Formula: =IF(C7>49,'Calorie-Wise Serving Chart'!$C$3)
• The 3D formula above checks the calories of people from 50 years
and above which is stored on the calorie wise serving chart
worksheet and output their calorie values on the calorie detector
worksheet.
VLOOKUP
• Excel’s VLOOKUP is used to look up a value or array of values from the left
most column of a table and returns the value on the same row from the
column you specify. You can Use VLOOKUP when you need to find things in a
table or a range by row. For example, look up a price of an automotive part
by the part number, or find an employee name based on their employee ID.
Its made up of the lookup value, the table array, the column index number
and the range lookup.
• The lookup value: is the value to be found in the first column of the table and
can be a value, a reference or a text string.
• Table Array: Is a table of text, numbers or logical values in which data is
retrieved. Table array can be a reference to a name or a range name
• Column Index Number: is the column number in table array from which the
matching value should be returned.
• Range Lookup: is a logical value to find the closest match in the first column
when sorted in ascending order
VLookup
• How to Lookup Values in a dataset using VLookup:
• Click Formulas
• Click Look Up and Reference
• Click VLookup
• Under Lookup value, Click on the Lookup Value
• Under Table array, highlight the entire dataset
• Enter the column index number (1 for column A, 2 for column B
etc.)
• Click Ok
HLOOKUP
• Excel’s HLOOKUP is used to look up a value or array of values from
the top row of a table and returns the value on the same column
from the row you specify.
• How to Lookup Values in a dataset using HLookup:
• Click Formulas
• Click Look Up and Reference
• Click HLookup
• Under Lookup value, Click on the Lookup Value
• Under Table array, highlight the entire dataset
• Enter the row index number
• Click Ok
Thank You