Module 9

• financial functions

  • excel functions used for analyzing loans, investments, and financial metrics
  • most deal with cash flow
• cash flow: direction of money to and from an individual or group
• present value: current value of a loan or investment
• 5 functions associated with loans and investments

  • fv function
  • pmt function: required payment for each period of loan/investment
  • nper function: number of payments
  • pv function
  • rate: interest
• Calculating payment for borrowing costs

  • PMT: determines payment made periodically
  • positive value in investments because its returns from the investment
  • negative value in loans because it's money spent to repay the loan
  • =pmt(rate, Nper, PV, [Fv=0], [Type=0]
  • rate: interest rate per period
  • nper: total number of payment periods
  • pv: present value of the loan or investment
  • - positive for loans
  • - negative for investments
  • fv: future value of the loan/investment after all payments
  • type:
  • type=0 : payments are at the end of periods
  • type=1 : payments are at the beginning of period
• Amortization Schedule pt 1

  • specifies how much of each loan payment is devoted toward interest and toward repaying the principal
  • Divided into
  • ipmt: the amount of a payment that is used to pay the interest of the loan
  • ppmt: calculates the amount used to repay the principal
  • principal: amount of the loan still unpayed
• • cumipmt: cumulative totals of interest payments
  • cumipmt(rate, nper, pv, start, end, type)
  • type=0 end of the period
  • type=1 start of the period
  • assumes FV is zero
  • start: starting payment period
  • end: end of payment period
  • amount spend in specific timeline
  • cumprinc: cumulative totals of principal
  • cumprinc(rate, nper, pv, start, end, type)
• Calculating fv

  • FV(rate, nper, pmt, [pv=0], [type=0])
  • pmt will be negative if it is a loan/investment
  • fv will be positive because the money is returned to the investor/lender
  • if it is a loan the present value will be positive
• calculating nper

  • calculates the number of payments required to repay a loan or reach an investment goal
  • NPER(Rate, pmt, pv, [fv=0], [type=0])
• calculating pv

  • calculates the present value of a loan/investment
  • for a loan pv will be the current size of the loan
  • for an investment pv is the amount of money initially placed in an investment account
• Amortization schedule 2

  • Ipmt: amount that is going to pay interest
  • ipmt(rate, per, nper, pv, [fv=0], [type=0])
  • per: indicates the payment period for which the interest is due
  • impt: how much a person will owe monthly in interest
• Amortization schedule pt 3

  • ppmt: amount used to repay the principal
  • ppmt(rate, per, nper, pv, [fv=0], [type=0]
  • Amount owed monthly going to principal
• Pmt

  ipmt+ppmt
• depreciation: process of allocating the original cost of an asset over the lifetime of the asset

  require you to know

  • asset's original cost
  • length of the asset's useful life
  • asset's salvage value (value at the end of its useful life)
  • rate at which the asset is depreciated

  included in the earnings part of the income and loss statements
• straight-line depreciation

  • loses value by equal amounts each year
  • SLN(cost, salvage, life)
• Declining balance depreciation

  • depreciates by a constant percentage
  • depreciation is highest in the asset's early lifetime
  • DB(cost, salvage, life, period, [month=12]
  • period: period for which you want to calculate the depreciation
  • month is used if the asset is used for less than a year
• depreciation changes the tax liability (how many taxes you owe) in the earnings section of the income and loss statements
• You can make a company look less or more profitable depending on what depreciation method you use
• Taxes and interest are added in the taxes liability section of the income statement
• extrapolation is used to extend a series form a single value or a few values to project future values

  • doesn't require a trend
  • requires a step vaue
• interpolation is used to fill in a series when you know the starting and ending values of that series

  • uses trend
• income statement sections

  • income: projected income from sales and cost of sales (cost of goods sold), marketing, and development
  • expenses: project general expenses incurred by fixed costs
  • earnings: estimates net profit and tax liability
• gross profit = sales revenues - cost of goods sold
• net profit = gross profit - expenses - taxes
• initial earnings = gross profit - total general expenses
• due diligence: investigation, audit, or review to confirm facts or details of a matter under consideration

  • examination of financial records before making a transaction or neogitation
• Rate function

  • calculates interest rate
  • Rate(Nper, Pmt, Pv, [Fv=0], [Type=0], [Guess]
  • used to calculate the return from investments
• payback period: length of time required for an investment to recover its initial cost

  • doesn't consider the time value of money
• time value of money: money today > same amount received later

  • is it better to receive 100 dollars today or 105 next year if you can put the 100 dollars in a savings account with a 6% interest rate?
  • next year you will have 106 in the savings
  • rate of return (discount rate): interest rate you assume for the present value of your investment (6% from the ex)
  • uses PV and FV function: negative result because it returns a value indicating how much you need to invest now to receive money later
• NPV Function

  • used if the future payments are not equal
  • NPV(rate, value1, [value2, value3, ...])
  • rate: rate of return
  • assumes payments occur at the end of each period and payments are evenly spaced
  • positive value because it calculates the value of those payments in today's dollars
  • *** initial investment (negative value) + NPV
• choosing a rate of return

  • the more riskier the investment the higher the rate of return
• IRR Function

  internal rate of return: point where NPV=0

  • higher irr are preferred
  • IRR(values, [guess=0.1])
  • values: cash flow values from the investment
  • compares investment to a savings account
  • must include a neg and positive values
  • assumes payments are evenly spaced
  • include the initial cost of the investment in the values list