calculation inside excel

Occasional Contributor

calculation inside excel

I tried to calculate and instead it gives me a negative #, using the formula =B1+NPV(B7,B2:B6) for:

The answer should be  16,312,073 and not the one that's showing as (22,760,060.76).

7 Replies

Re: calculation inside excel

Negative numbers aren't showing with parentheses in Excel

f you're using Excel and negative numbers aren't displaying with parentheses, you can change the way negative numbers are displayed. But if that doesn't work, or if the parentheses option (\$1,234.10) isn't available, it's likely because an operating system setting isn't set properly. If you're using a Mac, make sure you use the App Store and update to the latest version of macOS. If you're using Windows, use one of the following solutions to change the negative number format. Keep in mind that changing the setting will affect all programs on your computer, not just Excel.

Re: calculation inside excel

That's not the issue here. I already fixed the negative number situation. The issue is that I am getting a different answer from what it is supposed to be. The right answer is 16,312,073. But after following the instructions by entering the data and the formula and hitting ENTER, I am getting (22,760,060.76). Why is that? Solve it yourself and see which numbers did you come up with as the answer. Thanks.

Re: calculation inside excel

The formula is =B1+ NPV(B7+B2:B6).

Re: calculation inside excel

@Yabis_Mannmentions  wrote:  ``formula is =B1+ NPV(B7+B2:B6)``

No, it isn't.  It is as you wrote it the first time, to wit:  =B1+NPV(B7,B2:B6) .

-----

@Yabis_Mannmentions  wrote:  ``The answer should be 16,312,073``

Who says?!

You wrote:  ``after following the instructions ....``

It might be helpful if you provided a copy of the instructions.

Ideally, do not retype them yourself.  We all make mistakes.

If the instructions are provided in a file, attach the file; or upload it to a file-sharing website, and post the download URL.  I like box.net/files; others like dropbox.com.  The download URL should not require that we log in.

Alternatively, take a photo of the instructions and provide that image file.

-----

@Yabis_Mannmentions  wrote:  ``I am getting (22,760,060.76). Why is that?``

Because that is the correct result (well, one of two possible results) for the cash flows that you posted.

The following demonstrates the calculations.  Note that my cell references are different because I inserted a row with column titles.

Re: calculation inside excel.....Here is the question.

atural
Mosaic.
Natural Mosaic Company (U.S.) is considering investing
Rs55,000,000
in India to create a wholly owned tile manufacturing plant to export to the European market. After five years, the subsidiary would be sold to Indian investors for
Rs110,000,000.
A pro forma income statement for the Indian operation predicts the generation of
Rs11,500,000
of annual cash flow, is listed in the popup table,
.
The initial investment will be made on December 31, 2011, and cash flows will occur on December 31st of each succeeding year. Annual cash dividends to Natural Mosaic from India will equal
85%
of accounting income.
The U.S. corporate tax rate is 40% and the Indian corporate tax rate is
50%.
Because the Indian tax rate is greater than the U.S. tax rate, annual dividends paid to Natural Mosaic will not be subject to additional taxes in the United States. There are no capital gains taxes on the final sale. Natural Mosaic uses a weighted average cost of capital of
17%
on domestic investments, but will add six percentage points for the Indian investment because of perceived greater risk. Natural Mosaic forecasts for the rupee/dollar exchange rate on December 31st for the next six years are listed in the popup table,
.

a. What is the net present value and internal rate of return on this investment from the project's viewpoint?
b. What is the net present value and internal rate of return on this investment from the parent's viewpoint?

Re: calculation inside excel.....Here is the question.

Sales revenue
Rs37,000,000

Less cash operating expenses
(20,000,000)
Gross income
Rs17,000,000
Less depreciation expenses
(6,000,000)
Earnings before interest and taxes
Rs11,000,000
Less Indian taxes at
50%
(5,500,000)
Net income
Rs5,500,000
6,000,000
Annual cash flow
Rs11,500,000

Re: calculation inside excel.....Here is the question.

@Yabis_Mannmentions  wrote: ``After five​ years, the subsidiary would be sold to Indian investors for Rs110,000,000.``

That must be added to the last annual cash flow.  So the correct cash flow model is:

Formulas:

D2:  =B2+C2

D9:  =NPV(23%, D3:D7) + D2

D10:  =IRR(D2:D7)

That applies to Question #A.

I do not believe you provide sufficient information for us to answer Question #B, namely ``the​ rupee/dollar exchange rate on December 31st for the next six years are listed in the popup​ table``.

But I believe the following demonstrates the calculations.

Note:  Calculated amounts might differ due to rounding.

Formulas:

G3:  =IF(B3="", "", B3/F3)

H3:  =IF(C3="", "", C3*85%/F3)

I3:  =SUM(G3, H3)

I10:  =NPV(23%, I4:I8) + I3

I11:  =IRR(I3:I8)