Financial
Functions
This Using business math workshop will help you to understand how to use certain Financial Functions available through the Fun
wizard provided by Excel. The function wizard has many available financial functions, but we will only concern ourselves with
types in the beginning class. The functions we will review are the Future Value, Present Value, Rate, and NPer, although more
available.
To begin, please click on the Understanding Finance Functions worksheet, and review how each functio
works. Once you are sure of your understanding, click on the On Your Own worksheet and complete the f
questions. After completing the questions, save and submit for grading.
available through the Function
ly concern ourselves with a few
and NPer, although more are
iew how each function
eet and complete the four
ading.
UFF 1.
You are interested in determining how much you will need to
have saved up if you expect to retire in 20 years. Presently,
your montly requirements for expenses is at 2,500 a month, and
you expect to live for 20 years after you retire. You are
projecting the cost of living to stay at about 5%.
Time to retire:
Interest Rate:
Monthly Requirement:
Future value:
20
5%
2500
$1,027,584.17
What we are trying to find out is how much money do you think you
will need to have set aside for the future. Given that the cost of living
is at 5% (or so you project), and you want to retire in 20 years, and
currently need $2,500 a month to get by.
The interest rate is divided by 12, as the interest rate is on a yearly
basis, and we need to turn it into a monthly basis.
The number of periods is the amount of time we have until we expect
to retire. We expect to retire in 240 months. (The interest rate needs
to match the nper, (quarterly, monthly, yearly)).
The Pmt is the amount that we need each month. Notice it is a
negative number. That represents a cash outflow amount.
UFF 2.
Now that you know how much you will need, we need to look at
the payment necessary to have that much saved
Time to retire:
Interest Rate:
Future Value Needed:
Payment
20
8%
$1,027,584.17
$1,855.75
We need to determine the payment. Starting with the PMT financial
function, we will analyze the necessary parts.
The interest rate is divided by 12, as the interest rate is on a yearly
basis, and we need to turn it into a monthly basis.
The number of periods is the amount of time we have until we expect
to retire. We expect to retire in 240 months. (The interest rate needs
to match the nper, (quarterly, monthly, yearly)).
In this case we don't know the present value amount (as it isn't a
lump sum available right now), but we do know what we need in the
What pieces do i need t
financial problem?
Regardless of what we are solving for, eithe
value, or how long it will take to pay for so
questions involves knowing some form of t
value (how much did i borrow?), future va
future), interest rate, and number of paym
payment.
Each type of problem will be worked throu
and understand the missing component and
problem. Then, the On your own workshee
We need to determine the payment. Starting with the PMT financial
function, we will analyze the necessary parts.
The interest rate is divided by 12, as the interest rate is on a yearly
basis, and we need to turn it into a monthly basis.
The number of periods is the amount of time we have until we expect
to retire. We expect to retire in 240 months. (The interest rate needs
to match the nper, (quarterly, monthly, yearly)).
In this case we don't know the present value amount (as it isn't a
lump sum available right now), but we do know what we need in the
future. You will need only to use either the present value OR the
future value, there is no need to include both
UFF 3.
Fina
We want to buy a house, but can only afford $800 a month (not
including the taxes and other fees) How much of a house can
we afford if we have an interest rate of 6.5%, 30 year term?
Term
Interest Rate:
Home Price
Payment
30
7%
$126,568.66
$800.00
We know the payment, what we don't know is how much (Home
price, or present value). For this problem, we'd need to use the PV
function to determine what the initial loan amount would be to fit our
needs
UFF 4.
We are looking at buying a car, and our credit is less than
perfect. We have found one, and the dealer has said they could
get us the payment we want, but won't come out and tell us what
interest rate we are being charged.
Term
Interest Rate:
Principal
Payment
4
12.7%
$15,000.00
$400.00
Notice that the rate will return the interest rate per term (in our case
assessed per month), so we needed to multiply
the rate results by 12 to get the annual rate charged
=RATE(E78*12,E81,-E80)*12
Notice that the rate will return the interest rate per term (in our case
assessed per month), so we needed to multiply
the rate results by 12 to get the annual rate charged
=RATE(E78*12,E81,-E80)*12
What pieces do i need to solve a
financial problem?
Regardless of what we are solving for, either the present value, future
value, or how long it will take to pay for something, each of these
questions involves knowing some form of the basic components: present
value (how much did i borrow?), future value (what is it worth in the
future), interest rate, and number of payments i will make and
payment.
Each type of problem will be worked through, so that you can go through
and understand the missing component and understand the solution to the
problem. Then, the On your own worksheet is available for you to try.
OUO
1
What will the payment be for the cool stereo that you
want? The interest rate is at 21%, you want to finance it
for 3.5 years, and its cost is 1,500.
Interest Rate:
Term
Principal
Payment
Total Interest Paid
How much of this is going to interest? Multiply the payment
amount by the number of payments. Then subtract this
number from the original amount financed. The difference
represents interest paid for using someone elses money.
OUO
2
30year
You have a desire to pay off your home in 15 years rather
than 30. How much would the payment need to be if the
interst rate was 5.5%, the principal amount was $150,000.
More importantly, how much would you save by paying off
the house early?
Interest Rate:
Term
Principal
Payment
15Year Interest Rate:
Term
Principal
Payment
Total amount saved by paying off early:
In order to determine how much you'd save by paying of the
mortgage early, you'd need to multiply out the original payment
amount by the original term, and then subtract that amount
from the 15-year payment for the 15-year term
OUO
3
You are thinking of buying a car, but can only afford $175
a month for a payment. How much of a car can you afford
(principal) if the current interest rate is 4.5%, and you are
financing for 4 years?
Interest Rate:
Term
Principal
Payment
What is the unknown in this case? We don't know the present
value (principal), so you'd need to use the PV function
What is the unknown in this case? We don't know the present
value (principal), so you'd need to use the PV function
OUO
4
You have gone into rent america to rent/buy a television.
The payments are going to be $75 a month, for 2 years, on
furniture worth about $1,000. What interest rate are they
charging you?
Interest Rate:
Term
Principal
Payment