# Irr Calculations / Formula For Investment

Dec 17, 2008

I have a specific problem on irr calculations. In the excel file, I have following data.

date of investment - 1st May 2008
Investment Value - 1000000
Investment close date - 15th Dec 2008
Closure value - 1055000

I want to calculate IRR for the investment for the days the money got invested.
How do I calculate this in excel.

ADVERTISEMENT

## Calculating Annual Return On Investment For Real Estate Investment?

May 5, 2012

What formula to use to show the Annual ROI for a real estate investment property (single family). I believe I have all my figures to calculate WITH (acquisition amount, annual net income, etc.) -- but I can't figure out how to determine the ROI.

Or - is determining that % even possible/logical?

## Formula For Investment Gain

Oct 20, 2009

I was looking for how to calculate the value from on cell F11 from
this excel table but my formula didn't work so I'm looking for other solutions?
ur Cell F11 indicates \$104
and cell F12 \$320
and F13 \$658

but from my formula only works with on cell F11
I used this formula on this sheet.
=PMT(C7/12,\$A\$11*12,,E11)*-1

Enter Retirement Parameters as IndicatedSummary of Retirement CalculationsDate plan began1/1/2008Projected retirement date1/1/2052Number of years to contribute45TRUEAccount value at retirement\$542,654Annual contribution\$1,300 Total contribution over life of plan\$58,500Expected rate of return8%Investment Gain\$484,154Type of planRoth IRAPercentage through investment89%\$0.00 \$1 Contribution NumberDateValue prior to contributionContributionTotal Invested Over Life of PlanInvestment GainValue at end of the year11/1/2008-\$1,300 \$1,300 \$104\$1,404\$104 21/1/2009\$1,300 \$2,600 \$320\$2,920\$320 \$216 31/1/2010\$1,300 \$3,900 \$536\$4,436\$658 41/1/2011\$1,300 \$5,200 \$5,200\$1,127 51/1/2012\$1,300 \$6,500 \$6,50061/1/2013\$1,300 \$7,800 \$7,80071/1/2014\$1,300 \$9,100 \$9,100

## Add Zeros After Formula Calculations?

Sep 17, 2013

I have A1 that contain numbers, B1 that contain number, and i want to multiply those 2 and at the end of result i want to add zeros on front. Let me give you an example:

A1 field has data :00.375B2 has data: 6.49 C1 has the multiply result of A1+B1: 2.43 (But i want when the calculation is done and have the result 2.4 to add 2 zeros on front and become: 002.43 or if the result is for example: 65.20 to add 1 zero on front and become 065.20 , and if the result is 102.20 do not add anything on front.

So in total i need to have the 5 digits of number.

## Calculations Based Off Formula From Another Cell

Jul 8, 2014

I'm trying to do two things:

1. I'm trying to show the formula of specific cells in the work sheet. So far I've only found out how to show formulas on the entire worksheet. I imagine there has to be some way of me doing this without using macros.

2. Here's the doozy. What I'm trying to do is use the base code for a formula that is stored in one cell (Formula Page: B5:B7) and apply it to other cells (Calc Page: C4:C6). In other words, I want to apply the cell formula from B5:B7 to the cells C4:C6 and use them to calculate the TOTAL COST. I want it so that whenever I manipulate cells B5:B7, they are also appropriately manipulated in C4:C6.

Please see attached for reference. Attachment 33077

## Geometry Formula For Circle Calculations

May 10, 2009

See attached photo. I need a formula to calculate the length of line �c1�. I know the diameter of the circle, and the number of segments. In the photo there are 8 segments but this will change.

## Is A Single Formula Better Than Intermediate Calculations?

Feb 2, 2010

I come from a (procedural) programming background, where we perform operations step by step. I tend to use intermediate calculation columns, with only 1 or 2 calculations per formula, or, failing that, use a UDF. I find this easier to debug and maintain.

The solutions I see tend to give the result in one formula. This reminds me of the functional progamming languages I used years ago. I'd like your opinions on what is best-practice, or common practice, in relation to constructing formulas, so that I can improve my development.

## Variable Number For Use In Formula Calculations

Jun 19, 2008

I have a formula that will be used by others to obtain a certain result by altering a number in the formula. What I would like to do is make that number obvious as the one that can be changed. The formula reads - "=(M48+P48+Q48)*0.08453-(((J48+K48)*\$K\$4)*0.1)" -what I would like to do is color the 0.08453 in the Formula Bar so it stands out.

## Determine An Investment Max Value

Feb 14, 2007

I want to easily determine the max value of the stocks I own by only changing the daily value but i cannot bypass the circular reference problem. Example:

Stock|Today's Value| Max Value
A | 1 | 1
B | 3 | 3

Tomorrow i'm going to change today's value and my goal is that the max value remains unaltered if today's value is smaller. like this:

Stock|Today's Value| Max Value
A | 2 | 2
B | 1 | 3

## Table Of Calculations Down To A Single Array Formula

Feb 9, 2010

I'm trying to condense a table of calculations down to a single array formula, but am getting stuck on one piece of it.

The table data is very simple, and can be in just two columns:

1, value
2, value
3, value
4, value
5, value

Where 1 thru 5 are time periods, and values are various numbers.

I want to perform the GammaDist function on each value. It requires a time period input, and in this case it is the 1 thru 5 in the table. So at the end of period 5, the formula for the first value would be

=Gammadist(5,x,y,TRUE) (x,y values not important here)

and the formula for the second value would be:

=Gammadist(4,x,y,TRUE)

The only thing that changes is the period number.

So, my goal is to write an array formula that will sum the GammaDist for each of the 5 rows, for all timeperiods (which is 5 in this case)

I can get this far:

={SUM(B1:B5*GAMMADIST(ROWS(1:5)-1,C1,C2,TRUE))}

But this passes 1 thru 5 to all rows, I only want 1 thru 5 passed to row 1, 1 to 4 passed to row 2, etc.

## Sumproduct Formula Causing Slow Calculations

Aug 23, 2007

I have the following formula in 220 cells in a workbook:

=( SUMPRODUCT(('General Journal'!\$C\$1:\$C\$44995>=\$D\$3)*('General Journal'!\$C\$1:\$C\$44995<=\$E\$3)*('General Journal'!\$H\$1:\$H\$44995=\$A5),'General Journal'!\$I\$1:\$I\$44995))+(SUMPRODUCT(('General Journal'!\$C\$1:\$C\$44995>=\$D\$3)*('General Journal'!\$C\$1:\$C\$44995<=\$E\$3)*('General Journal'!\$J\$1:\$J\$44995=\$A5),'General Journal'!\$K\$1:\$K\$44995))+(SUMPRODUCT(('General Journal'!\$C\$1:\$C\$44995>=\$D\$3)*('General Journal'!\$C\$1:\$C\$44995<=\$E\$3)*('General Journal'!\$L\$1:\$L\$44995=\$A5),'General Journal'!\$M\$1:\$M\$44995))

Where D3 is a starting date, E3 is a closing date, and A5 is a sorting code. It looks at my general journal and looks for all the entries between the two dates, and then sums up all the entries linked to the sorting code in A5. The sorting code is in column H, and the amount to sum is in column I, and this is repeated 3 times.

You are already probably signering at how long I must have to wait while excel calculates all these formulas (* 220). I have resorted to a macro that turns caluculation to manual when I open this workbook, and back to auto when it closes.

## Calculating Monthly Investment

Apr 21, 2007

If I want to obtain a future value of \$500,000 at the end of 20 years, how much do I need to save each year at an interest rate of 10% per annum? I ended up using the following formula: =PMT(10%,20,-PV(10%,20,,-500000,1),,1). Let's define (Insert/ Name/Define) the answer to this function as Pmt1.

By then using =FV(10%,20,Pmt1,,1) to confirm that Pmt1 will end up providing \$500,000 after 20 years I get the answer I am looking for but have absolutely NO IDEA why it works. Worse, I do not know whether it is the correct answer. I have the following function (courtesy of someone) that I use to determine the expected future value of a series of annual payments at a fixed interest rate but also with fixed annual increases in the payments. (Example: \$1000 per annum is invested for 20 years. The interest earned on the \$1000 is 10% per annum. The \$1000 increases by 5% each year - i.e. 19 increases)

=Pmt1* SUMPRODUCT((1+5%)^(ROW(OFFSET(\$A\$1,0,0,20,1))-1),(1+10%)^(20-ROW(OFFSET(\$A\$1,0,0,20,1))+1))

Assuming the payment does not increase, I simply replace the 5% with 0%. When I run this function and use Pmt1 as the annual payment the answer differs from the one that I get from the PMT function that I quoted above until I change the ",,1" in the function to ",,0". What do I not understand about these functions!? Which is correct or are both provided I learn to know what they do? This is the vaguest question I've ever been able to devise simply because I can see that something is amiss and I do not know what - or how to start figuring out what it is that I "know not"!

## Purpose Of Investment Calculators

Oct 24, 2007

I am part of an investment sales team and was wondering if the use of specific excel based investment calculators will assist me in my task of advising clients of the best options. Also; if these tools do help, I wanted to create a calculator that displayed the interest schedule depending on the term that its invested for. I have searched and found various threads on this topic; though not too sure how

## Average Net Investment Calc In One Cell

Sep 29, 2008

How to calculate ANI in one cell? The only way I can currently find the result is to build an amortisation schedule and then divide the sum of the outstandings by the payment frequency.

I'd like to avoid building the amort.

## Calculate A Pay Back Period For A Certain Investment

Dec 6, 2006

I need to calculate a pay back period for a certain investment. Excel has options for NPV and IRR but not for the Pay back period. (PBP). I've tried finding a solution using google discussion groups and some books but nothing helped me out. attached you'll find an example of my sheet. I need the PBP to be calculated in e.g. 5,3 years. but when the figures change it should automaticly recalculate the PBP.

## Change Event For Conditional Formatting Not Firing On Formula Calculations

Sep 25, 2007

I have used the code for formatting: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm. This works fine when typing in the numbers manualy but if I have a formula (eg:A1=b1+c1) and a1 = 15 nothing happens. Is there an update button or a better way to do this?

## Assign Individual Investment To Master Spreadsheet

Dec 17, 2012

(i) I have a spreadsheet listing all the investments details. For example,

Investment Investment Date Amount
xxx company 1/1/2012 \$10,000
yyy company 2/1/2012 \$20,000

(ii) Each investments have different investors. For example, xxx company has two investors: A and B; yyy company has 4 investors: B, C, D, E

I want to assign Investors Name and their Personal Investment Amount to the main investment spreadsheet. So that I can retrieve individual investor's investment positions.

For example, I want a spreadsheet showing B's investment. Then this spreadsheet should have details of xxx company and yyy company, as well as B's personal investment amount.

## Macro- Data That Is Exported Our House Investment System

Oct 27, 2008

i have a spreadsheet with data that is exported to Excel via our in house investment system, the report looks something like below, though real data consists of 2000 rows of data. Where we have O/S in Bank this means these entries are all physical bank entries i.e statement credit and statement debit, and where we have O/S not in Bank these are all accounting entries, i.e. Ledger Debit and Ledger Debit.

What i am after is a macro that will insert a column next to Team and then input SDR SCR on all statement entries and LDR and LCR on all Ledger entries, the final report should look like the second spreadsheet....

## Investment Calculator: Sheet That Will Take Payments Away From A Starting Balance

Jan 16, 2007

I need to create a sheet that will take payments away from a starting balance. The catch is that I don't know the starting balance. The interest is compounded annually at 3% and each withdrawl is different. My sheet needs to show the interest earned in each year and the account balance like a running sheet, after lets say 5 years there needs to be no money left.

## Calculate 10% Or 20% On Investment Form Depending On Interest Rate In Column

May 25, 2009

The way I have this sheet setup is to calculate a 20% fee off the interest earned column "D". Say you earned 6.5% on a beginning balance so the interest earned is in col "D". This works fine for a 20% fee but I need the fee to calculate a 10% fee if the interest in column "C" hit 4% or below and it also needs to be able to calculate a 20% fee if the interested earned is above 4%.

## Investment Selection With Cell Selection

May 4, 2007

I'm trying to build an investment calculator. Tried searching "calculator" as keyword but return nothing similiar.

There are eight cell : [ A ][ B ][ C ][ D ][ E ][ F ][ G ][h]

[ H ] is price of the leverage

[A] is X balance
[b] is X lot size
[C] is X amount of money use to get x lot
[D] is X percentage of money use to get x lot
[E] is X point gain
[f] is X money gain
[G] is balance + money gain

Ignore the formulae for leverage to determine 1 lot price. Take 1 lot = 250.

What I'm looking for is when I enter any value at cell B, C or D, it will automatically calculate the value for either B, C or D cell.

Let say, the balance is 10000.

If I enter 2 at cell B. Then, it will calculate the value for cell C & D.

If I enter 750 at cell C, then it will calculate value for cell B & D.

If I enter 25 at cell D, it will calculate value for cell B & C.

how to use OR Selection in excel.

## Time Calculations.?

Feb 9, 2010

if you look at attachment, in Cell J2, if cell I2 is greater than 2 minutes but less than two minutes, the I need the value of I2 in Cell J2 otherwise 0:00:00 then K2 between 3 & 4 Mins and so on.

## Certain Line Only Calculations

Jan 27, 2010

I am making a spreadsheet that tells us the following information about prints that we do:

## Turn Off Calculations

Feb 2, 2010

I have attached a sample file. All the data is inserted into this file via a text file, except one column "G". Someone in this forum was kind enough to help me in being able to include the city name 'G' to a parcel number 'A'. However, the page is constantly trying to complete 'Calculations' and won't let me do anything without first hitting 'Control Break'.

This is fine except that when I try to save the file into .txt, I don't have the option of 'Control Break' and I have to end up closing the file. As the 'real' file has over 100,000 rows, nothing happens very fast. I have tied changing the 'Calculation Options' but that doesn't seem to change anything.

## Set Range For Calculations Without VBA?

Jul 11, 2012

Is it possible to have an input box for a range of calculations and then in my formulas set the range to anchor + variable ?

Something roughly like:

Range = 20

Product( F13:F & Range_Variable_Cell_Value)

## Slow Calculations

Mar 19, 2007

I'm working working with this spreadsheet that is moving incredibly slow. Every time I enter anything, it takes anywhere from 10 seconds, to a couple minutes to calculate and let me proceed. It is a pretty big file (4.60 MB), but I also work with another spreadsheet that is a little smaller (2.95 MB) that has never taken more than a fraction of a second to calculate anything. What could I do to spead up the spreadsheet?

## Depreciation Calculations ...

Sep 15, 2007

My problem is the following:

I'm trying to model straight line depreciation over 5 years for certain investments. This could for example be done like this: ....

## Changes To Status Bar Calculations

Sep 2, 2008

Does anyone know of a site or some code that can make changes to add some items to the status bar calculations?

Such as right now you have Sum, Count, Count Nums, Max, Min... just to name a few.

I'd like to be able to add some other ones down there if it can be done. I'd like to count #N/A's or possibly sum only positive numbers? I can do it with a quick keyboard shortcut and message box with a macro, but I'd like to just be able to highlight a range and have it show up like sum or the others do.

Just thought I'd psoe the question.

## Userform Calculations

Feb 21, 2009

im in the process of designing a userform but it needs to have some calculations in it, and im lost on how to do it if anyone can help

ie

in textbox1 there is a value of 5 and in textbox2 there is a valuve of 10 and in text box 3 i want to appear textbox1 multiplied by textbox2 so 50 should appear

this is a basic example but as soon i have mastered that i can adapt everything else to my userfom

will the above be done automatically as soon as i enter values or would i have to "make the calculation" via a button or something

and also i want to have a enter button or something like that that copies the data that i have put in the various text boxes to the excel spreadsheet and clear the userform cells

phew thats best i can explain any questions just reply to this post as per the norm

## FIFO Calculations

Feb 21, 2010

I have inventory... with starting product at a certain cost, received product at a new cost, and used product. I want to assume that we are using FIFO.

What I need is a total cost (what I have paid) for what is sitting in my freezer.

Column A is START (5)
Column B is RECEIVED (6)
Column C is USED (2)
Column D is END (9) or (A1+B1-C1)
Column E is OLD COST (\$12.20) cost per unit of those 5
Column F is NEW COST (\$13.50) cost per unit of the 6 i got in

So I need in Column G a FIFO formula for total cost of what I have in the fridge.

Copyrights 2005-15 www.BigResource.com, All rights reserved