# ROI based on NAV

#### mba_110

##### New Member
Hi Every one

I want to calculate the following in excell with formulas, I have tried some but not satisfied.

 A B C D E Particulars Date Amount Qty 1 Invested Date 29/08/16 2 Investment Amount 15,000 1251.114 3 Average NAV 29.5417 4 Current NAV & Date 29/11/16 30.1441 5 If in loss NAV & Date 29/11/16 28.5412 6 Need Absolute return 7 Need Annual return

<tbody>
</tbody>

Please note quantity will remain same whether NAV go up or down, i want results in % and amount both.

Any help here is much appreciate.

Last edited:

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
If you can provide more details, i will try to help you out.
To get your results, kindly explain what is your criteria and how NAV is calculated (Mathematically).
Give some sample data with desired results will be helpful.

NAV is net asset value of any investment mostly in strategic funds or Mutual funds called it NAV to address the price of fund for investors.

When I invest I bought units of this fund (1 Unit = NAV of fund) so if I invest 15,000 the equal quantity of units will be allotted to me based on that particular day’s fund NAV so the calculation is like 15,000 divide over that day’s NAV = will give you the quantity of units.

Only NAV is change every day based on funds performance quantity remained same, so I need to calculate the absolute return of investment on particular date from the date I have first invested in this fund let say I have invested on 29/08/16 so if I want the result for yesterday i.e. 11/12/16 or quarterly, half yearly or annually or any number of period I want it should give that, I have a cell for that date on which it should calculate.

Average NAV mentioned because investment could be done in multiple times with different NAV of dates so to make it simple or to quick reference I have mentioned the average NAV of all invested amount over the period.

Replies
3
Views
477
Replies
12
Views
659
Replies
3
Views
926
Replies
6
Views
294
Replies
1
Views
715

1,196,052
Messages
6,013,112
Members
441,748
Latest member
MrBigglesworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back