Intro
- cost benefit analysis
- perform complex calculations, determine investments, model financial
- date functions, calc future & present val of investments, create loan schedules, cal the net presentval & internal rate of return, depreciation functions
Discussion
- Rate (RATE)
- Payment Period (NPER) - by month
- Present Value (PV)
- Future Value (FV)
- Net Present Value Func (NPV)
- Internal Rate of retunr (IRR)
- Financial based date functions
- End of Month function, EDATE()
- Straight Line Depreciation (SLN), Sum of Year Digits, Double Declining balance
Working with Dates (EOMONTH, EDATE, WORKDAY.INTL)
- E.g: Financial Reports
- Due Date -
=EOMONTH(C5,0), 0 = end of the month for the date we've selected, 1 - the end of the month for the previous month, 2 - end of month for this quarter (3 months time) - workday and workday international function
=WORKDAY.INTL(EOMONTH(C5,0),10,1,K5:K7), K5:K7 - holiday days
- Reminder Date -
=EDATE(C5,2)
Financial Functions (FV, PV, PMT)
- Client investment queries
- FV (Future Value) - how much an investment will be worth in the future
- PV (Present Value) - amount you're going to invest or borrow at the current time
- RATE (Rate) - the rate you're going to earn per Payment Period (NPER)
- PMT (Payment Amount) - regular payment we'll be making monthly or annually
- Value of investment at the end of 25th year:
=FV(B3,B4,B5,B6,0), 0 - end of period - Amount to be invested now -
=PV(B3,B4,B5,B6) - Amount to be invested at the end of every year -
=PMT(B3,B4,B5,B6)
Loan Schedule (PMT, EDATE)
- Calculate what the repayments would be on a loan
- Period Per year
=IF(C10="Monthly",12,1) - Repayment Periods (NPER) =
- Rate per Period
=C8/C12 - Monthly Payment
=-PMT(G7,C13,C7)negative because the value is outgoing - Projected Interest
Net Present Value and Internal Rate of Return (NPV, IRR)
Depreciation Functions (SLN, SYD, DDB)