Purpose of Assignment The purpose of this assignment is to allow the student to calculate the project cash flow using net present value (NPV), internal rate of return (IRR), and the payback methods. Assignment Steps Resources: Corporate FinanceSTEP 1 (SUBMIT AS A WORD DOCUMENT IN MEMO FORMAT)Create a 350-word memo to management including the following:

Describe the use of internal rate of return (IRR), net present value (NPV), and the payback method in evaluating project cash flows.

Describe the advantages and disadvantages of each method.

Minimum required reference includes your textbook.STEP 2 (SUBMIT AS AN EXCEL SPREADSHEET)Calculate the following time value of money problems using Microsoft® Excel®: :

If you want to accumulate $500,000 in 20 years, how much do you need to deposit today that pays an interest rate of 15%?

What is the future value if you plan to invest $200,000 for 5 years and the interest rate is 5%?

What is the interest rate for an initial investment of $100,000 to grow to $300,000 in 10 years?

If your company purchases an annuity that will pay $50,000/year for 10 years at a 11% discount rate, what is the value of the annuity on the purchase date if the first annuity payment is made on the date of purchase?

What is the rate of return required to accumulate $400,000 if you invest $10,000 per year for 20 years. Assume all payments are made at the end of the period.

What is the project cash flow generated for Project A and Project B using the NPV method? Which would you choose and why? Assume a 10% discount rate.

What is the payback period for Project A and Project B using the payback method? Which would you choose and why?

Project A and Project B:

week_3_assignment.docx

Unformatted Attachment Preview

Don't use plagiarized sources. Get Your Custom Essay on

Using the Payback Method, IRR, and NPV

Just from $13/Page

#1 PV OF SINGLE LUMP SUM

PRESENT VALUE OF SINGLE LUMP SUM

If you want to accumulate $5,000 in 10 years, how much do you need to deposit today that pays an

interest rate of 2%? This means ONE single lump sum invested at ONE time.

If they are asking you to find the value TODAY, this means they are asking you to find the

PRESENT VALUE. So, use your PV function in Excel as you are dealing with compounded interest.

Click formulas, insert function, financial, PV.

Variables are as follows:

Rate: .02 (this is your annual rate of interest)

Nper: 10 (this is the total number of compounding periods – in this case, you have been earning

interest on an annual basis for 10 years)

Pmt: 0 (not applicable as there are no payments)

FV: 5000 (this is your future value or value at the end of the period)

Click ok and you should get $4101.74 as your PV (ignore the negative as the negative in a PV value

for Excel simply means money out or the initial investment “out” of your pocket).

#2 FV OF SINGLE LUMP SUM

FUTURE VALUE OF SINGLE LUMP SUM

What is the future value if you plan to invest $1,000 for 7 years and the interest rate is 10%? This

means ONE single lump sum invested at ONE time.

Use your FV function in Excel. Click formula, insert function, financial, FV. Variables are as follows:

Rate: .10 (this is your annual rate of interest)

Nper: 7 (this is the total number of compounding periods – in this case, you have been earning

interest on an annual basis for seven years)

Pmt: 0 (not applicable as there are no payments)

PV: -1000 (this is the value at the beginning of the period – remember to enter as a negative in Excel

to indicate money out since this was your initial investment “out” of your pocket)

Click ok and you should get $1948.72 as your FV.

#3 RATE OF INTEREST EARNED

RATE OF INTEREST EARNED

What is the interest rate for an initial investment of $1050 to grow to $1500 in 1 year?

Use your RATE function in Excel. Click formulas, insert function, financial, RATE.

Variables are as follows:

Nper: 1 (this is the total number of compounding periods – in this case, you have been earning

interest on an annual basis for one year only)

Pmt: 0 (not applicable to this problem)

PV: -1050 (this is the value at the beginning of the period – remember to enter as a negative in Excel

to indicate money “out”)

FV: 1500 (this is the future value or value at the end of the period)

Click ok and you should get .4286 or 42.86% as your rate.

#4 PV OF ANNUITY

PRESENT VALUE OF ANNUITY

If your company purchases an annuity that will pay $1,000/year for 20 years at a 15% discount rate,

what is the value of the annuity on the purchase date if the first annuity payment is made on the date

of purchase?

If they are asking you to find the value TODAY (in this case they say value on the purchase date

which is TODAY), this means they are asking you to find the PRESENT VALUE. So, use

your PV function in Excel as you are dealing with compounded interest. Note however, they are

asking you to find the PV of the series of payments and not the value in the future. Also, this

is VERY IMPORTANT, they say that the first payment is made on the date of purchase. So that

means that this payment is made IN ADVANCE as there wasn’t time for interest accrue. So, you will

need to note that under your Type variable. Click formulas, insert function, financial, PV.

Variables are as follows:

Rate: .15 (this is your annual rate of interest)

Nper: 20 (this is the total number of compounding periods – in this case, you have been earning

interest on an annual basis for 20 years)

Pmt: 1000 (this is the amount of each payment you will receive)

FV: 0 (this is your future value or value at the end of the period – in this case, there is no future value

as when the payments are done, the annuity is worth zero)

Type: 1 (this means that the first payment was made in advance or prior to interest accruing – read

the problems carefully as some payments are made in advance and some are made at the end of

the period which is the norm so in those cases you would simply ignore the type variable)

Click ok and you should get $7198.23 as your PV (ignore the negative as the negative in a PV value

for Excel simply means money out or the initial investment “out” of your pocket).

#5 RATE OF RETURN

RATE OF RETURN

What is the rate of return required to accumulate $3000 if you invest $100 per year for 10 years.

Assume all payments are made at the end of the period. Note that payments are made at the end

of the period so we can ignore the type variable.

Use your RATE function in Excel. Click formulas, insert function, financial, RATE.

Variables are as follows:

Nper: 10 (this is the total number of compounding periods – in this case, you have been earning

interest on an annual basis for one year only)

Pmt: -100 (this is the amount of the payment you will make so it is money “out” of your pocket so it

needs to be noted with a negative)

PV: 0 (this is the value at the beginning of the period and there is no lump sum value today to worry

about as all you are doing is making series of payments)

FV: 3000 (this is the future value or value at the end of the period)

Click ok and you should get .2292 or 22.92% as your rate.

#6 NPV CALCULATION

NPV CALCULATION

What is the net present value of a project with an initial cost of 50000 and cash inflows of 10000,

15000 and 25000 with a discount rate of 10%.

For this problem, PIECE OUT THE CASH FLOWS. So your equation here is:

Net Present Value = Purchase Price + Present Value of Cash Flows or abbreviated as PP + PVCF

PP: -50000 (This is given and in your equation, this should be a negative to indicate money out as

this is an outflow.)

PVCF: Use Excel and the NPV function. Click formula, insert function, financial, NPV. Variables are

as follows:

Rate: .10 (This is your discount rate in DECIMAL form.)

Value 1: 10000 (first year cash flow)

Value 2: 15000 (second year cash flow)

Value 3: 25000 (third year cash flow)

Click ok and you should get 40270.47 as your PVCF value.

#7 PAYBACK PERIOD

PAYBACK CALCULATION

Danica, Inc has the following mutually exclusive projects:

Project

Project

Project A

Project B

A

B

Year

Cumulative

Cumulative

Cash

Cash

Inflows

Inflows

Flows

Flows

0 -20000

NA

-23000

NA

1 12000 12000 13000 13000

2 8500

20500

9500

22500

3 2900

23400

8500

31000

What is the payback? Which would you choose?

Payback is simply the amount of time it takes for the inflows to equal your original outflow (initial

investment). This does NOT take into account time value of money, so no Excel formulary is

necessary. Review the chart above. I added a couple of columns above to tally the Cumulative

Inflows for each project as I like to lay out my variables at the beginning and see how the totals pan

out.

For project A, you can see that payback is going to be before the end of Year 2 (this is the year you

would recoup your 20000), but what fractional part of Year 2?

So, you have ONE full year and a partial of the second year.

Payback Period = Number of Full Years + (Initial Investment – Full Year(s) Cumulative Inflow)/Cash

Flow in Year of Payback

Payback Period = 1 + (20000-12000)/8500

Payback Period = 1 + (8000/8500)

Payback Period = 1+.9411

Payback Period = 1.941 years

For project B, you can see that payback is going to be before the end of Year 3 (this is the year you

would recoup your 23000), but what fractional part of Year 3?

So you have TWO full years and a partial of the third.

Payback Period = Number of Full Years + (Initial Investment – Full Year(s) Cumulative Inflow)/Cash

Flow in Year of Payback

Payback Period = 2 + (23000-22500)/8500

Payback Period = 2 + (500/8500)

Payback Period = 2+.0588

Payback Period = 2.059 years

Since Project A has a shorter payback, that is your winner!

Now all you do is add your PP and PVCF values together (round to 2 decimals) and you should be

good to go.

NPV = PP + PVCF

NPV = -50000 + 40270.47

NPV = -9729.53

This project should actually be REJECTED as the NPV is negative. If you had a choice

between two mutually exclusive projects, you would choose the one with the higher NPV.

…

Purchase answer to see full

attachment

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.