Highline Excel Class 50: Value Asset: PV NPV & XNPV function

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 stumped...again...

Have 5 columns (A1-E1) with different bond data: (respectively)
Coupon, Maturity, Settlement, Price, Yield (asset swap is F1)

Depending on the type of swap I am performing i want the ASWAP (user-created) function to reference different values within the row.

For example: Swap 1 = settlement, maturity, coupon, price, curve
(curve is a user input single number 1-3)

Swap 2 = settlement, maturity, yield, price, curve (yield in place of coup)

Swap 3 = settlement, maturity, yield, 100, curve (100 for par redemption)

etc. I won't type out all the iterations, not necessary.

The way that I want the ASWAP to recognize which Swap# to use is by inputting a number into a cell beside the Aswap column that I can input 1, 2, 3, etc., and then recalculate to get the desired outcome. Want to keep this a static reference as well as the price at par (each owning their own single cell, not a fill down the columns because there are too many).

So assuming G1 is the Swap# input, and H1 is 100...

How do i write a formula that is capable of reading the input in G1 and outputting the proper swap calc.

My gut feeling was along the lines of nested IF's with the corresponding formula... but i won't mess around in futility on here.

Let me know if this is too confusing without me putting up an example.

Thanks in advance for any color that can be given to this.
Rob

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

I'm trying to use a class for defining a set of data and also to get values from two different ways, a database and a spreadsheet. Let me explain with an example.

I created a class called clsData with the following code:

Public conn As ADODB.Connection
Private p_isin As String
Private p_tick As String

Public Property Let isin(myvalue As String)
p_isin = myvalue
End Property
Public Property Get isin() As String
isin = p_isin
End Property
Public Property Let tick(myvalue As String)
p_tick = myvalue
End Property
Public Property Get tick() As String
tick = p_tick
End Property


I inserted a public function inside that class, in order to retrieve the data in the two mentioned ways:

Public Function GetData(myvalue As String, DB As Boolean) As clsData
Dim rs As ADODB.Recordset
Dim sql As String
Dim c As Range
Dim myData As clsData
Set myData = New clsData

If DB Then
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
sql = "SELECT * FROM dbData WHERE isin='" & myvalue & "'"
rs.Open sql, conn

If Not rs.EOF Then
myData.isin = rs("isin")
myData.tick = rs("tick")
End If
rs.Close
Else
With ActiveSheet
Set c = .Range("A:A").Find(myvalue, LookAt:=xlWhole)
If Not c Is Nothing Then
myData.isin = .Cells(c.Row, 1)
myData.tick = .Cells(c.Row, 2)
End If
End With
End If
GetData = myData
End Function

All that code goes into clsData. Now I add a standard mode with this code:

Public Function GetClassData() As clsData
Set GetClassData = New clsData
End Function

Sub myTest()
Dim AllData As clsData
Set AllData = New clsData
AllData = GetClassData.GetData("FI0009000681", False)
End Sub

The first function is in order to access the class and the function inside
The routine myTest is trying to retrieve the data I need

I get an error in last line of the function: "GetData = myData". Error message window says "Object variable or block With not established" (actually it is a translation of my current Excel version)

Anybody knows what is wrong with this code?
I can send the whole code to anybody who can help

JC

I am trying to to calculate the XNPV of a stream of cash flows. The problem
is that when I run differet scenarios through my model the cash flows are in
different years each time. Is there any way that I can get excel to
automatically calculate the XNPV of cash flows for only the years where my
cash flows are greater than zero or where my XNPV Dates are greater than
zero? Thanks in advance for your help!

Hi All,

I've been battling the use of XNPV trying to reconcile the result I am getting between a Nominal cash flow and Real cash flow.

Normally I would have my periods in years and use NPV on both the Real and Nominal models with no problem. This is pretty much the first time I've used XNPV.

The problem is that the XNPV result that I was getting from my Real and Nominal models is different. After trying to find the error in my model I moved to looking at XNPV and the result it was giving me. I have pulled together an example that hopefully describes my problem adequately.

Firstly I created a one year time period in months, put in a real cash flow line, escalated by inflation (divided by 12) to get my nominal cash flow. Using XNPV I get a Real NPV of 543.378 and a Nominal NPV of 545.014.

At first I thought this might be something to do with the way I was using XNPV, so next I set the time line to be 12 years in yearly periods, and adjusted the Nominal cash flow accordingly (not divided by 12). The result produced a Real NPV of 121.85 and a Nominal NPV of 125.43.

Finally, as a sanity check I did a straight NPV of the annual cash flows and I get and NPV of 110.98 for both the Real and Nominal cash flows.

Hopefully this makes sense and is easy to follow...

Am I making a fundamental mistake? or is the XNPV function always like this?

Can someone help me use this function (or point me towards another one that will give me the rate of return) when the rate is negative?
Thanks.

Steve

Hi all,

I'm having trouble using the XNPV function in excel.

I've outlined my problem in the attached file, where I have a $billion loan (lucky me) and i make interest payments at random dates.

I thought that if I were to discount these payments at the effective (average weighted by days) interest rate over the whole period, the XNPV value should come to my initial amount ($1bn).

This does not appear to be the case in my problem.

I don't think I'm using the XNPV correctly, can anyone please help?

Thanks!

Hello Friends,
i would like to ask about
What's the difference between NPV and XNPV ? with Detailed if you can and giving Example for each case

thanks for your attention

Dear All,

I have a doozie! I was wondering if anyone who is an expert at Mathematics/excel can tell me why the yield calc in the attached spreadsheet does not equal the discount rate. It is only marginally out but I am racking my brains as to why!

Please help the yeild should equal 12.5% exactly
Anyone who knows the answer I will be bloody impressed!!!!