Time value analysis has many applications. For example, you use time value of money concepts in valuing stocks and bonds, establishing loan payment schedulesThink of four examples in your organization or from your personal life, or a combination of both,

NPV

Formula: =NPV(discount_rate, cash_flows) Use this formula for valuation analysis to determine the Net Present Value (NPV) of a series of cash flows, either how much a company is really worth or how much an investment in a project is worth.

The discount rate is the interest rate used to determine the present value of future cash flows. This helps determine if the future cash flows from a project will be worth more than the initial capital needed to fund the project. Do not confuse the Cost of Capital with the Discount Rate! The cost of capital is the minimum rate needed to justify the cost of a new project and meet the finance costs. The discount rate is the number that is needed to meet or exceed the cost of capital. Many companies calculate their weighted average cost of capital (WACC) and use it as their discount rate when budgeting for a new project (see the WACC tab).  

WACC

Formula is:  (E/V x Re)  +  ((D/V x Rd)  x  (1 – T)) The weighted average cost of capital (WACC) is a financial metric that shows what the total cost of capital (the interest rate paid on funds used for financing operations) is for a firm. E = market value of the firm’s equity (market cap) D = market value of the firm’s debt V = total value of capital (equity plus debt) E/V = percentage of capital that is equity D/V = percentage of capital that is debt Re = cost of equity (required rate of return) Rd = cost of debt (yield to maturity on existing debt) T = tax rate  

IRR

Formula: =IRR(cash flows) Use it to determine the internal rate of return for a series of cash flows.

MIRR

Formula: =MIRR(cash flows, cost of borrowing, reinvestment rate) This a is a variation of the internal rate of return that is very important for finance professionals.  The M stands for Modified, and this formula is particularly useful if the cash from one investment is invested in a different investment.   If the business is high returning and produces an 18% IRR, but the cash along the way is reinvested in a bond at only 8%, the combined IRR will be much lower than 18% (it will be 15%, as shown in the example below).

PMT

Formula: =PMT(rate, number of periods, present value) Used to work with real estate financing and it is like a mortgage payment calculator. For a given loan amount, interest rate and time frame to repay (usually years or months) you can calculate how much the payments will be (includes both principal and interest).

IPMT

Formula: = IPMT(rate, current period #, total # of periods, present value) Use this formula to calculate the interest portion of one fixed payment.  You can calculate the principal payment in each period by taking the difference of PMT and IMPT.

EFFECT

Formula: =EFFECT(interest rate, # of periods per year)   Use to calculate the effective annual interest rate for non-annual compounding.   

DB

Formula: =DB(cost, salvage value, life/# of periods, current period)   Use to avoid building a large Declining Balance (DB) depreciation schedule. It is used to calculate your depreciation expense in each period.

RATE

Formula: =RATE(# of periods, coupon payment per period, price of bond, face value of bond, type) Use it to calculate the Yield to Maturity for a security.  This is useful when determining the average annual rate of return that is earned from buying a bond.

PV

With an interest rate of 5%, what is the current value of $12 million dollars if you will receive it in 20 years? (Lump sum in the future.) =pv(rate,nper,pmt, [fv],[type])

What is the Present Value of putting $1,000 a year into an interest bearing bank account with 2.25% rate for 10 years? (Basic Annuity Problem.)

If you know that you can sell something (as asset) in 5 years for $250,000 and that the standard discount rate for that asset is 5%, what should you pay for the asset now? The discount rate refers to the interest rate used in discounted cash flow (DCF) analysis to determine the present value of future cash flows. This helps determine if the future cash flows from a project or investment will be worth more than the capital outlay needed to fund the project or investment in the present. (What to pay now.)

FV

With an interest rate of 5%, what will the value of a $10 million dollar investment today be worth in 5 years? (Lump sum in the future.) =fv(rate,nper,pmt, [pv],[type])

If I invest $1,000 per year for 10 years with an interest rate of 4.55%, how much will I have in 10 years? (Basic Annuity Problem.)

If you can buy something now for $1,000 or that same item 2 years from now for $1,100 with expected inflation at 4.5% per year, which item costs less? In other words, should I buy it now or wait two years? (When to buy.)

BETA

Formula: =SLOPE(dependent variable, independent variable)   Use it to calculate the Beta (volatility) of a stock given the weekly returns for a stock and the index (or another stock) used to compare it to. Beta = Riskiness as compared to another stock or index

FINANCIAL RATIOS

AFN & pro forma statement

Exchange Rate

If euros sell for $1.50 (U.S.) per euro, what should dollars sell for in euros per dollar?

Cross Rates

At today’s spot exchange rates 1 U.S. dollar can be exchanged for 9 Mexican pesos or for 111.23 Japanese yen. You have pesos that you would like to exchange for yen. What is the cross rate between the yen and the peso; that is, how many yen would you receive for every peso exchanged?

Foreign Investment Analysis

Chapman Inc.’s Mexican subsidiary, V. Gomez Corporation, is expected to pay to Chapman 30 pesos in dividends in 1 year after all foreign and U.S. taxes have been subtracted. The exchange rate in 1 year is expected to be 0.10 dollars per peso. After this, the peso is expected to depreciate against the dollar at a rate of 4% a year forever due to the different inflation rates in the United States and Mexico. The peso-denominated dividend is expected to grow at a rate of 8% a year indefinitely. Chapman owns 10 million shares of V. Gomez. What is the present value of the dividend stream, in dollars, assuming V. Gomez’s cost of equity is 13%?

Foreign Capital Budgeting

Note that the rate of return in Swiss francs is 18.2524% vs. 20% in U.S. dollars because the Swiss Franc in the forward market is expected to sell at a premium to the U.S. dollar. Thus, the U.S. dollar return is reduced by the appreciation of the Swiss Franc to the U.S. dollar.

Currency Appreciation

Suppose that the exchange rate is 0.60 dollars per Swiss franc. If the franc appreciates 10% against the dollar, how many francs would a dollar buy tomorrow?

Purchasing Power Parity

A computer costs $500 in the United States. The same model costs 550 euros in France. If purchasing power parity holds, what is the spot exchange rate between the euro and the dollar?

Interest Rates Parity

The nominal yield on 6-month T-bills is 7%, while default-free Japanese bonds that mature in 6 months have a nominal rate of 5.5%. In the spot exchange market, 1 yen equals $0.009. If interest rate parity holds, what is the 6-month forward exchange rate?

Spot and Forward Rates

Boisjoly Watch Imports has agreed to purchase 15,000 Swiss watches for 1 million francs at today’s spot rate. The firm’s financial manager, James Desreumaux, has noted the following current spot and forward rates: On the same day, Desreumaux agrees to purchase 15,000 more watches in 3 months at the same price of 1 million Swiss francs. a. What is the cost of the watches in U.S. dollars, if purchased at today’s spot rate? b. What is the cost in dollars of the second 15,000 batch if payment is made in 90 days and the spot rate at that time equals today’s 90-day forward rate? c. If the exchange rate for is 0.50 Swiss francs per dollar in 90 days, how much will Desreumaux have to pay (in dollars) for the watches?