Exam pre1

TVM Functions in EXCEL

Order of Variables = (Rate, Nper, Pmt, PV, Fv, Type, Guess)

Future Value = FV (Rate, Nper, Pmt, PV, Type)

Present Value = PV (rate, nper, pmt, fv, type) （PV代入计算其他时看正负）

No. of Periods = NPER (rate, pmt, PV, fv, type) (要根据要求代入全部周期如5年*12=60个月)

Payment = PMT (rate, nper, PV, fv, type)

Rate = RATE (nper, pmt, PV, fv, type, guess) (计算每个周期的率如年率12%/12个月=1%月率) –pv

Type 0 or omitted at the end of the period 1 at the beginning of the period

Format:

1. Year

2. Account balance at beg. Year

3. Deposit at beginning of year

4. Interest earned during year

5. Total in account end of year

单变量求解：如果结果变化，原来数字与结果之间的公式不变，那么原来数字应该是多少？

A: NPV function: (Homework 1 Exercise 1)

NPV：=NPV (Discount Rate, Cash flow (year 1): Cash Flow (year n)) +Cash Flow (year 0)

Use Data Table to compute from discount rate of 0%, 4%, 8%… 48%

1. 第一列Discount rate 从空格，然后是0%，然后到要求的48%

2. 第二列 NPV 空格的右方填入：=正常计算出来的NPV

3. TABLE: 全选，数据，假设分析，数据表，引用列，只输入Discount rate

4. 插入，散点图，带图表和数据划线的散点图选择数据进行编辑数据名称为NPV，X轴为从0%开始的NPV 到48%，Y轴为从NPV开始的所有NPV。

Internal rate of return of the project：=IRR（Cash flow(year 0):Cash flow(year n)）

在图表中显示，选择数据，添加，名称为IRR，在X轴选择IRR。

B: FV TABLE & FV FUNCTION: (homework 1 Exercise 2)

注意审题：end or beg or middle, 以month 还是year 作为period, interest 怎么compound

Interest compound: EAR: =effect (rate, n)代入FV的公式里

Deposit paid in the middle: EPMT: =PMT*((1+EAR) ^ (rest period/total period)

C: Retirement:及 table需要计算: (Homework 1 Exercise 3)

Number of months: =现在距退休的year*12

Yearly Rate=告知

Monthly interest rate: =yearly rate/12

Monthly deposit: =pmt () n=month, monthly rate, unknown=blank, fv=-required amount, type

Annual deposit to savings=monthly deposit*12

Data table: rate, monthly payment,

D: Mortgage to finance the purchase: (Homework 1 Exercise 4) PMT Function

Form: Monthly payment: =pmt () Mortgage rate is monthly compounded rate=year rate/12

1. Monthly Periods: from 1 to n year*12

2. Principal at beg of period: 1. =mortgage principal 2…=prin cipal at end of last period

3. Mortgage payment=$monthly $payment same

4. Interest paid: =IPMT ($Monthly $rate, period, $n $month,-$mortgage $principal)

5. Repayment for principal: =PPMT ($Monthly $rate, period, $n $month,-$mortgage $principal)

6. Principal at end of period: = Principal at beg of period-Repayment for principal

E: RATE function (using IRR function) annual interest rate (Homework 1 Exercise 5)

Loan principal告知

Monthly repayment告知

Number of months=year*12

Monthly interest rate: =rate (n=month, -pmt, PV=loan principal)

Annual interest rate: = ((1+Monthly interest rate) ^12)-1

To get IRR need the table: =IRR (pmt in table (month 0): pmt in table (month n*12))

1. Month 0 to n*12

2. PMT: Loan principal (month 0). –Monthly payment (from month 1 to month n*12)

F: How long to pay off loan. Use loan table. (Homework 1 Exercise 6)

Loan amount告知

Interest rate告知

Annual payment 告知

How long to pay off the loan? =NPER (rate=yearly rate, -pmt=-annual payment, PV=Loan amount)

Loan table:

Year:计算结果取上，如7.6取8

Account balance Beginning: Loan amount for y1 and Account balance end last period for others

Annual deposit Beginning: =$annual $payment 因为不是完全的8年，所以最后一个period如第8年，取=account balance end last period(即period 7)+annual interest paid(period 8).

Annual Interest paid: =account balance beginning* $interest $rate

Account Balance End: =Account balances beg-(annual deposit beg-annual interest paid)

G: Continuously compounded discount rate. (Homework 1 Exercise 7)

(Use 365-day per year convention)

1. Present value each year: =EXP (-$continuously compounded discount $rate *Date)*Cash flow

Present Value: =sum (PV (date 1): PV (date n))

2. Time (years) from today: =DATEDIF ($date today, date, "d")/365

Present value each year: = EXP (-$c c discount $rate *time from today)*cash flow

Present value: = sum (PV (date 1): PV (date n))

H: Savings amount (Homework 1 Exercise 8)

1. Compounded interest rate =EFFECT (stated (nominal) annual interest rate, npery)

Npery: daily=365, monthly=12, yearly=1.

FV: =FV (rate=c interest rate, n=year, pmt=0, PV=-savings amount)

2. 两年不同，第一年为R1 第二年为R2compounded semiannually

Compounded interest rate= (1+R1/2) ^2*(1+R2/2) ^2-1

FV: = savings amount *(1+ Compounded interest rate)

I: Savings amount (Homework 1 Exercise 9)

1. 求C interest rate 一样以annual nominal 求出的是annual ear 在计算PV时所以要/12

Pre=PV (rate=Compounded interest rate /12, n=year*12,pmt=savings amount,没有,type=0或1)

2. Interest is compounded continuously: Compound Interest =LN (1+ Interest Rate)

J: Grade (Homework 1 Exercise 10)F4绝对引用

1.根据给出grade range制作表格，如0对应F，以分界点作为第一列，等级作为第二列。

2. =VLOOKUP (分数,$选择自己做的分数成绩对照表:$选择第几列作显示，是否绝对或近值)

K: 求值公式（Homework 1Exercise 11）包括

1. Average: =Average (:) Median: =Median (:)

2. =Large/small (不仅要选择区域，最后还要选择第几个，如，最大第一个，就填1)

3. =Max/Min(只要选择区域就行)

4. Sample variance: =var (:) /9

5. Population variance: =varp (:) /10

6. Sample standard deviation: =stdev (:)

7. Population standard deviation: =stdevp(:)

8. CORRELATION: =CORREL (x:x, y:y) return

9. Covariance: =COVAR(x:x,y:y)

10. Regression intercept: =INTERCEPT

11. Regression slope: =SLOPE

12. Regression r-squared: =RSQ y=rs*x+ri r2=rr 数据，数据分析，回归

13. =rank(选数据，$选:表格) =count(:) =counta(:) =countif(数据，条件)见EXCEL 33.

L: IRR guess (Homework 1 Exercise 12)

其他：

1. 在一个单元格里分行：[Alt]+ [Enter]

2. =DATEDIF(第一个日子,第二个日子,"？")？=m month, d day, y year

3. =XIRR(第一个pmt :最后一个pmt,第一个时间：最后一个时间)需要加载ADD-IN VBA

XIRR computes the daily internal rate of return

4. =XNPV(RATE,VALUE第一个：最后一个，时间第一个：最后一个)