Excel Finance Trick #15: PV & NPV don't work: XNPV function

I need to write an Excel function that returns best price of a government bond.

The actual handwritten formula is quite complicated, impossible to display
here, and the input values for this formula are-----------------
Accrued days, Number of coupon period days, Days from settlement to next
coupon date, coupon periods per year, number coupon periods between
settlement & redemption, annual yield, discount rate.

I asked a similar question for the "sum" function, but the answer I got for it will not work for the "XNPV" function, which is what I really wanted. So I will ask it here (again, sort of). I have cells A1 through B4

1/1/06 50
2/1/06 40
3/1/06 20
4/1/06 30

I want to calculate

XNPV(10%, A1:A4, B1:B4), XNPV(10%, A2:A4, B2:B4), XNPV(10%, A3:A4, B3:B4),
XNPV(10%, A4:A4, B4:B4),

with the starting point B? dependant on the date I enter in another cell C1 (the date
in C1 would of course correspond to a date in B1 to B4), some kind of
"lookup" function embedded in the XNPV function?

Thanks


--
CARMEN HOVENDICK

I am struggling with the NPV and XNPV formulas in Excel. I have the following cash flows and using a discount rate of 25%, I am getting VERY different answers. Why? The difference should be minimal given the periods are slightly different (days in a month). Also, what answer do you get if you discount each cash flow at 25% and then do a simple sum? Doing that, I get approx $36.5 million which is "close" to the XNPV.

NPV: $171,170
XNPV: $29,057,451

Formulas a
NPV: (25%, B1:B34)
XNPV: (25%, B1:B34, A1:A34)


1/31/11 $(68,400)
2/28/11 $(45,000)
3/31/11 $(21,600)
4/30/11 $(160,000)
5/31/11 $23,400
6/30/11 $46,800
7/31/11 $68,400
8/31/11 $91,800
9/30/11 $115,200
10/31/11 $136,800
11/30/11 $160,200
12/31/11 $183,600
1/31/12 $183,600
2/29/12 $183,600
3/31/12 $183,600
4/30/12 $(296,400)
5/31/12 $183,600
6/30/12 $183,600
7/31/12 $2,930,760
8/31/12 $2,917,750
9/30/12 $2,904,740
10/31/12 $2,891,730
11/30/12 $2,878,720
12/31/12 $2,865,710
1/31/13 $2,852,700
2/28/13 $2,839,690
3/31/13 $2,826,680
4/30/13 $2,471,170
5/31/13 $2,800,660
6/30/13 $2,787,650
7/31/13 $2,774,640
8/31/13 $2,761,630
9/30/13 $2,748,620
10/31/13 $2,722,510

Work four summers, starting at age 16

Save the income in a Roth IRA account

Invest it in a simple, low-cost equity portfolio

Simmer slowly for 47 years

Serve ungarnished (and untaxed) at age 67

If your money is invested in common stocks and you achieve the average
compound annual rate on large-capitalization U.S. stocks, 10.7%, your
account will grow to $9,378 at the end of the fourth year. You will be
20 years old. Invested in the same way, with no additional savings, the
account will grow to:
$25,917 by the time you are 30

$71,625 by the time you are 40

$197,943 by the time you are 50

$547,037 by the time you are 60

And $1,114,423 by the time you are 67

is there a function that will give me this answer without using
multiple rows and columns to calculate this.

Hey everyone I am new to programing in Excel and I do not know enough to wright a function that finds variance of a portfolio. This is what i have so far its messy and does not work.

Function VARPORT(Sa,Wa,Sb,Wb,Sc,Wc,Sd,Wd,Se,We)

va=VAR(Sa)
vb=VAR(Sb)
vc=VAR(Sc)
vd=VAR(Sd)
ve=VAR(Se)


cab=Covar(va,vb)
cac=Covar(va,vc)
cad=Covar(va,vd)
cae=Covar(va,ve)
cbc=Covar(vb,vc)
cbd=Covar(vb,vd)
cbe=Covar(vb,ve)
ccd=Covar(vc,vd)
cce=Covar(vc,ve)
cde=Covar(vd,ve)

VAPORT=((Wa^2)*va)+((Wb^2)*vb)+((Wc^2)*vc)+((Wd^2)*vd)+((We^2)*ve)+(2*Wa*Wb*cab)+(2*Wa*Wc*cac)+(2*Wa *Wd*cad)+(2*Wa*We*cae)+(2*Wb*Wc*cbc)+(2*Wb*Wd*cbd)+(2*Wb*We*cbe)+(2*Wc*Wd*ccd)+(2*Wc*We*cce)+(2*Wd*W e*cde)
End Function

The S stands for a list of stock returns.
The W is what percentage of the portfolio is spent on that stock.
The Covar and Var functions are to calculate the variance of each stock and the covariance between the five.
The three letters are C for covariance and then what stock will be looked at.

This is very confusing to me and I know I just made a big mess, can anyone help or point me int he right direction.

Good morning,

trying to find out what to do with my spreadsheet and the formulas in it.

If you look into the file 369. I can get to the stage where it outlines the mismatch figure. In the example it is 6 Mismatches.

But I cannot seem to get the table belwo right. Everytime I try and copy past the formula, the first set of data is the one from the first line and then I get a #NUM error, due to the fact that there seems to be a number reoccurence somewhere.

I took the youtube tutorial, but even copying that formula did not work.

My table consist of 2 sets of 600 values each.

Can someone tell me why I am getting this error and help me resolve the issue that the result is an existing set of data?

THANK YOU

BayerStars23
[IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot-1.png[/IMG]

hi i have writtern the following in a comman button

sub trythis()
dim x as range
set x = range("b1:b20")
for each x in x
if x.text = "June" then
x.copy
end if
next x
end sub

I have in the range names of the month repeated randomly. June appears 6 times in the list

By executing the above code the last cell containing "June" gets into copy mode (the cell is filled with marchings ants effect). Although the loop goes through many cells containing "June" only the last cell containing "June" shows this effect, how extend this effect to all the cells containing the text "June".

When you are making a formula say

=A5 + A6+A7

when you are doing this in cell D423, it sometimes gets annoying to have to either type in A6 and A7 manually or to navigate to A6 and A7.

I know there is a key you can hold down after doing A5 that keeps you on A5 even after putting in the + sign, so that you don't have to navigate all the way back there.

I can't figure it out!

Does anyone know what this is?

Please help!!

Thanks,
Michael

I am following this tutorial from youtube. It is a search function to search for a specefic word in an entire row. Here is the first part of the code:

Code:

=SEARCH(C$3,Table2[[#This Row],[DESCRIPTION]])

except i only get a value in the cell that i typed this in, instead of it searching the rest of the row like in the tutorial. i can't figure out how to fix this. anyone know what im doing wrong?

Hello,

I am constructing a make-shift database in excel but can't seem to find a way to get magic trick #213 to work.

What I am trying to do is create a dynamic filter via 16 criteria. I would like any results that match all criteria that I specify and then displays results in a separate table to the right.

Thank you so much for your help - I would attach my spreadsheet to this message if I knew how.

BR,

PR