Financial Modeling Exam 1
1. Explain the differences between Excel functions PMT, IPMT, and PPMT.
What type of cash-flow stream is each of these based on? When should each
of these be used? Which vary with time in your amortization schedule? Why?
- correct answer ✔Each of these is for use with an annuity, or annuity-type
cash flow (constant cash flows over a set period of time at regular intervals).
PMT calculates the total payment, which is the same for every period. IMPT
calculates the interest payment for each period, which will differ as the
remaining balance on the loan changes over time. PPMT calculates the
principal payment for each period, which will also change over time. These
change over time because the interest goes down as the balance of the loan
goes down, so more of the total payment goes to principal, and less goes to
interest. PMT should be used to calculate the overall payment for every
period, while IPMT and PPMT should be used for each period individually
(because they change each period).
1. What type of cash flow stream does Excel's PV and NPV functions not
handle well? - correct answer ✔These functions do not handle perpetuities
(cash flow streams that continue forever) very well. They only handle cash
flow streams with finite cash flows. (They also do not handle cash flow
streams that do not occur at regular intervals. For example, if you got one
cash flow in a year, another in 18 months, and a third at 36 months. You can
make this work in those formulas, but they will not deal with it themselves. We
only talked about part of this in class, which is these functions can only do
calculations for finite cash flow streams that occur in regular intervals).
In class, we tied our dividend forecast to sales. Why is this a bad assumption
in reality? In your answer, explain this both from a mechanical perspective,
and from the perspective of how this differs from what companies actually do.
What would be a more reasonable assumption? - correct answer ✔There are
two problems with this assumption. First, it's not what firms actually do. Most
firms try to keep dividends constant, or want dividends to grow at a constant
rate. (So they really would have a separate plan for dividends, and change on
things to make this work, like issuing new debt or equity if needed). Second,
, sales do not really translate directly to cash, and we need cash to pay
dividends. A better assumption would be to tie dividends to something closer
to cash, like net income. (You could also make an argument for basing
dividends on cash flow from operations and investing, since this tells you how
much cash flow you have before financing. Or you could assume that the firm
will keep dividends constant, or that dividends will grow at a constant rate.)
Extra principal pay - correct answer ✔Present Value of the difference
between what you will owe minue what you want to owe.
Use an annuity formula over that time period
How does Excel treat a PMT function - correct answer ✔Excel treats this as
if you're the borrower. Therefore FV is negative because it is an extra
payment the borrower is making
Sensitivity Analysis vs. Scenario Analysis - correct answer ✔Sensitivity
changes one variable and looks at the change in the dependent varible while
the scenario changes up to two variables and looks at reaction in the
dependent variable
What is Excel doing in the Goal Seek function? - correct answer ✔plugs in
slightly different numbers until it finds the solution to the answer. For example,
it will mess with different IRR levels until it finds one that sets NPV=0
What is Excel doing in the NPV function? - correct answer ✔Excel assumes
that your time starts 1 year from now, which is why you take out CF0
Change in NWC is listed as negative because - correct answer ✔it is listed
as a decrease on the statement and it is a cash outflow
1. Explain the differences between Excel functions PMT, IPMT, and PPMT.
What type of cash-flow stream is each of these based on? When should each
of these be used? Which vary with time in your amortization schedule? Why?
- correct answer ✔Each of these is for use with an annuity, or annuity-type
cash flow (constant cash flows over a set period of time at regular intervals).
PMT calculates the total payment, which is the same for every period. IMPT
calculates the interest payment for each period, which will differ as the
remaining balance on the loan changes over time. PPMT calculates the
principal payment for each period, which will also change over time. These
change over time because the interest goes down as the balance of the loan
goes down, so more of the total payment goes to principal, and less goes to
interest. PMT should be used to calculate the overall payment for every
period, while IPMT and PPMT should be used for each period individually
(because they change each period).
1. What type of cash flow stream does Excel's PV and NPV functions not
handle well? - correct answer ✔These functions do not handle perpetuities
(cash flow streams that continue forever) very well. They only handle cash
flow streams with finite cash flows. (They also do not handle cash flow
streams that do not occur at regular intervals. For example, if you got one
cash flow in a year, another in 18 months, and a third at 36 months. You can
make this work in those formulas, but they will not deal with it themselves. We
only talked about part of this in class, which is these functions can only do
calculations for finite cash flow streams that occur in regular intervals).
In class, we tied our dividend forecast to sales. Why is this a bad assumption
in reality? In your answer, explain this both from a mechanical perspective,
and from the perspective of how this differs from what companies actually do.
What would be a more reasonable assumption? - correct answer ✔There are
two problems with this assumption. First, it's not what firms actually do. Most
firms try to keep dividends constant, or want dividends to grow at a constant
rate. (So they really would have a separate plan for dividends, and change on
things to make this work, like issuing new debt or equity if needed). Second,
, sales do not really translate directly to cash, and we need cash to pay
dividends. A better assumption would be to tie dividends to something closer
to cash, like net income. (You could also make an argument for basing
dividends on cash flow from operations and investing, since this tells you how
much cash flow you have before financing. Or you could assume that the firm
will keep dividends constant, or that dividends will grow at a constant rate.)
Extra principal pay - correct answer ✔Present Value of the difference
between what you will owe minue what you want to owe.
Use an annuity formula over that time period
How does Excel treat a PMT function - correct answer ✔Excel treats this as
if you're the borrower. Therefore FV is negative because it is an extra
payment the borrower is making
Sensitivity Analysis vs. Scenario Analysis - correct answer ✔Sensitivity
changes one variable and looks at the change in the dependent varible while
the scenario changes up to two variables and looks at reaction in the
dependent variable
What is Excel doing in the Goal Seek function? - correct answer ✔plugs in
slightly different numbers until it finds the solution to the answer. For example,
it will mess with different IRR levels until it finds one that sets NPV=0
What is Excel doing in the NPV function? - correct answer ✔Excel assumes
that your time starts 1 year from now, which is why you take out CF0
Change in NWC is listed as negative because - correct answer ✔it is listed
as a decrease on the statement and it is a cash outflow
