Entering numerical sequences when sequential values are an ...



FNR 407

Lab. Exercise #1

Handout to accompany Excel spreadsheet

Due at end of class

Summation Notation

Note:

Capital letters are columns in the spreadsheet

j is the index across columns or rows

Numbers are rows in the spreadsheet

10. Entering numerical sequences when sequential values are an initial value plus a constant incremental value, e.g. 1, 2, 3; or 2, 4, 6

1. Enter the starting value of the sequence

2. In the next cell enter a formula giving the next value as the starting value plus the amount of the increment.

K

L42 = ∑ j 42

j = B

where, “42” is row 42 in the spreadsheet, and

B through K are columns in the spreadsheet. Thus, this notation means,

B42 + C42 + . . . + K42

11. Entering numerical sequences when sequential values are an initial value plus a constant incremental value, e.g. 1, 2, 3; or 2, 4, 6

1. Enter the starting value of the sequence

2. In the next cell enter a formula giving the next value as the starting value plus the incremental value.

K

D46 = ∑ j 46

j = B

where, 46 is row 46 in the spreadsheet, and

B through K are columns in the spreadsheet

12.

59

B60 = ∑ Bj

j = 50

where, B is the column in the spreadsheet, and

50 through 59 are rows in the spreadsheet

13.

74

D76 = ∑ (Bj * Cj)^2

j = 65

14.

90

D91 = ∑ (Bj * Cj)/(1.03^2)

j = 81

15.

103

E104 = ∑ (Bj - Cj)/((1+$D$96)^Aj)

j = 98

EXCEL Financial Functions

Future Value Function

|FV(rate, number of periods, payment, present value, type) |The value of an investment at the end of the term (0 if omitted) |

|Argument |Description |

|Interest rate, enter as “x%” |Annual compound rate of interest |

|Number of periods |Term of the investment |

|Payment (negative values indicate payments into an investment, |Annual payments when individual amounts are the same |

|positive values indicate payments out of an investment) | |

| Present value |Starting value that’s in addition to the annual payments (See FV |

| |Example 2) |

|Type |When payment is to be made (0 if omitted); 0 = at end of period; |

| |1 = at beginning of period (In class I’ll refer to this as year |

| |0) |

FV Example 1. You plan to contribute $4,000 in an Individual Retirement Account (IRA) at the beginning of each year. You expect the average rate of return to be 6% per annum. You are now 30 years of age. How much should be in the account when you are 65 years of age?

FV(6%, 35, -4000,,1) =$472,483.47

FV Example 2. Building on FV Example 1, assume you started your IRA 3 years ago. The amount in the IRA is $7,500 to start with.

FV(6%,35,-4000,-7500,1) = $530,129.12

Present Value Function

|PV(rate, number of periods, payment, future value, type) |Value of an investment today, i.e. in year zero (beginning of |

| |year 1) |

|Argument |Description |

|Interest rate, enter as “x%” |Annual compound rate of interest |

|Number of periods |Term of the investment |

|Payment |Annual payments when individual amounts are the same |

|Future value |Ending value that’s in addition to the annual payments (See PV |

| |Example 2) |

|Type |When payment is to be made (0 if omitted); 0 = at end of period; |

| |1 = at beginning of period (In class I’ll refer to this as year |

| |0) |

PV Example 1. You have the opportunity to invest $4,000 in a start-up company. The entrepreneur guarantees you $1,000 per year for the next 5 years in return for taking a risk on his enterprise. If you don’t make this investment you would buy a 5-year certificate of deposit (CD) with a 4.5% rate of return. Which is your best option, ignoring the difference in the risk?

PV(4.5%,5,1000,,0) = -$4,389.98 (function could be entered as PV(4.5%, 5,1000)

Since the PV of the payments is greater than the amount you would be investing the investment in the start-up would provide a greater rate of return than the CD, i.e. the start-up is the best option.

PV Example 2. What if the entrepreneur offers you $5,000 at the end of 5 years, instead of $1,000 per year for 5 years?

PV(4.5%,5,,5000,0) = -$4,012.26 (function could be entered as PV(4.5%,5,,5000)

The PV is still greater than the $4,000 investment so the investment is acceptable, but not as good as the annual payment option. TAKE AWAY – Getting your money back sooner, rather than later, is always better financially.

Net Present Value Function

|NPV(rate, value1, value2, . . .,value n) |Present value of a series of periodic inflows and outflows from |

| |an investment |

|Argument |Description |

|Interest rate, enter as “x%” |Annual compound rate of interest |

|value1, value2, . . ., value n |Series of annual net amounts |

|NOTE: all payments are assumed to occur at the end of each year. |

NPV Example 1. You have the opportunity to buy for $1,500,000 a farm with all the acreage suitable for wildlife habitat. Over the first year you’d need to make $100,000 of improvements. Thereafter you would have annual expenses of $25,000 and annual rental income of $75,000. You expect to retire in 10 years at which time you would sell the farm for an estimated $3,000,000. Your alternative rate of return (hurdle rate) is 6%. Is this a good investment?

NPV(6%,-100000,50000,50000,50000,50000,50000,50000,50000,50000,50000,3050000)-1500000 = $401,679.25

Since the NPV is greater than zero this investment would earn you more than the 6% alternative rate of return you require. It’s a good investment.

Payment Function

|PMT(rate, number of periods, present value, future value, type) |annual or monthly payment needed to pay off an amount borrowed in|

| |year 0 |

|Argument |Description |

|Interest rate, enter as “x%” for annual payments, “x%/12” for |Annual compound rate of interest, or equivalent monthly rate |

|monthly payments | |

|Number of periods, for monthly payments multiply the number of |Term of the loan |

|years by 12 | |

|Present value |Amount borrowed in year 0 |

|Future value |Lump-sum payment at end of period |

|Type |When payment is to be made (0 if omitted); 0 = at end of period; |

| |1 = at beginning of period (In class I’ll refer to this as year |

| |0) |

PMT Example 1. You want to buy a new log truck for $120,000 with a $10,000 down payment and the balance paid off over 5 years with payments made at the end of the year. The bank will charge 5.5% interest.

PMT(5.5%,5,110000,,0) = -$25,759.41

PMT Example 2. What would the monthly payments be for the loan in example 1.

PMT((5.5%/12),(5*12),110000,,0) = -$2,101.13

PMT Example 3. How much would you have to pay each month into a savings account earning 5.5% interest if you wanted to have $120,000 to buy a new logging truck in 5 years.

PMT(5.5%,5,,120000,0) = $1,742.14

Rate Function

|RATE(number of periods, payment, present value, future value, |Given the amount of payments made, it estimates interest rate |

|type, guess) |needed to pay off a loan, or to have a set amount at the end of |

| |the period. |

|Argument |Description |

|Number of periods |Number of years or months |

|Payment |Amount of annual or monthly payment made |

|Present value |Amount to be paid off |

|Future value |Amount needed in the account at end of period |

|Type |When payment is to be made (0 if omitted); 0 = at end of period; |

| |1 = at beginning of period (In class I’ll refer to this as year |

| |0) |

|Guess |Your estimate of the interest rate. This becomes the starting |

| |point for the iterative process used. |

RATE Example 1. You are planning on buying a tract of forestland as an investment. There is a tract available for $550,000. You can afford to $60,000 a year. If you want to pay off the loan in 15 years what interest rate would you need to negotiate to make this work?

RATE(15,-60000,550000) = 6.9000%

RATE Example 2. If instead or borrowing money to make a forestland investment you want to save the money first. You think you’ll need $500,000 in 10 years to buy a tract and you can afford to save $40,000 a year. What rate of interest would the account have to earn to achieve your goal?

RATE(10,-40000,,500000) = 4.8669%

Internal Rate of Return Function

|IRR(values, guess) |For a given array of cash flow values the rate of interest that |

| |makes the NPV for this array equal to 0 is estimated |

|Argument |Description |

|Values |An array of net annual values. Must be at least 1 each of |

| |positive and negative values. |

|Guess |Starting interest rate for the iterative process |

|NOTE: This function doesn’t account for year 0 net revenue | |

IRR Example 1. You’re considering the possibility of starting a business. The array of revenues and expenses by year is:

|Row in Excel |A |B |C |D |

|1 |Year |Revenue |Expense |Net Revenue (revenue - expenditures |

|2 |1 |$0 |$140,000 |-$140,000 |

|3 |2 |$10,000 |$55,000 |-$45,000 |

|4 |3 |$200,000 |$10,000 |$190,000 |

|5 |4 |$150,000 |$15,000 |$135,000 |

|6 |5 |$90,000 |$20,000 |$70,000 |

IRR(D1:D6,10%) = 37.2558%

This would be an exceptionally good investment.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download