Excel Finance Trick #16: Work Life & Retirement Finance

I'm trying to set up a formula that will determine the last amount of cash flow in a stream of payments that is required to reach a certain return. For example, if I have these cash flows YR 0 -50,000,000, YR 1 4,922,955, YR 2 7,086,093, YR 3 8,486,159, I want a formula that will determine how much cash flow I need in YR 4 to achieve a 10% IRR. Any suggestions?

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.

hey guys, so i am trying to import

HTML Code:

http://finance.yahoo.com/q/hp?s=MSFT

that type of information into excel via macro. i can do it by importing it via webquery but the problem is that i need more than one page (go all the way down and there is a NEXT button to get information that is older)

so i am trying to import not just one page, but lets say import 5 or 6 (which will give me a years data on a stock vs. the 3 months)

and then down the road, id like to get rid of the line that shows the dividend payout, but that isn't too important right now.

is this possible what i am asking?

Hi

I have made a excel sheet where i have put the names of several stocks in which i am interested. I have also used the facility of Import external data-New web query and given the Yahoo finance link
However, i am having trouble in importing the data as it says the file name should not contain ? wherein the yahoo web query contains the ? in the middle.
Is there any solution.
The surprising part is that i can edit the query and get the rates but it does not refresh, which means that i have not given the wrong path.
Also 1 out of 10 times it gets refreshed but 9 times it gives invalid web query
I shall be thankful if the members can help me out
I am attaching my Excel sheet for ready reference
Thanks

I am setting up my portfolio in yahoo finance and wanted to know if there is a way to link it up with my microsoft 2003 excel spreadsheet and yahoo stock portfolio so that I can have it automatically update in real time.

Please help

Hi,

I have a challenge building a financial model in excel. I'm modeling the profit margin of a potential contract with different time frames. Unfortunately, due to upfront revenue (with no underlying cost) incurred only in year 0, my profit margin % decreases as I extend the time frame. This causes problems with contract evaluation as it would conclude that shorter contracts are always better.

For example

Category YR 0 YR 1 Total
Rev 30,000 100,000 130,000
Costs 0 70,000 70,000
Net Profit 30,000 30,000 60,000

Margin 46%

Hello- I am using the Web Query function in Excel 2002 to import data from Yahoo Finance into an Excel sheet. I get the data OK but when i am trying for instance to use stocks quotes for financial calculations (like basic sums, perf, graphs..), those aren't in a format that work; i tried to change the format into a number one, but that doesn't work either. Would you have any advice ? Attached is one example (thanks Rob for the advice)

I'd just like to hear a comparison

well i dont know weather this is possible or not with Excel 2007, I did this with Google Docs Spreadsheet long before and it was wonderfull.

So hereby I call all genius here to tell me if this is possible.

As the Title says, i want real time stock price in my Excel 2007 from Google finance.
This is easily possible if i have my portfolio ready & i dont add new stocks daily in my portfolio, I simply go to Data > From Web and then somehow i import the Stock Price with Yellow or Green arrow in my excel file.

But my query is little bit different.

For example, Say if

A1 has a value MSFT

then B1 should be http://www.google.com/finance?q=msft
(I used formula =HYPERLINK("http://www.google.com/finance?q="&A1&"")

So now i have a hyper link or URL of Google finance page from which i need to import data.

Now my question is, Is there any possibility that excel opens that links as soon as I enter a new value like GOOG in A1 and import the Stock price automatically in cell C1.

As i have lots of stocks, i dont want to import data manually everytime i add a new stock in my portfolio. I need a formula by which i get the latest stock price as soon as i write a Stock Name in cell A1.

Example

A1 = MSFT
B1 = http://www.google.com/finance?q=msft
C1 = 29.60

A2 = GOOGLE
B2 = http://www.google.com/finance?q=GOOGLE
C2 = 570

It doesnt matters if B1 uses some different formula then Hyperlink, as i am concerned about values in A & C only.

So if i shorten my query in a single sentence, then i would say.

If i write some stock name in A1, then C1 must show its current market price.

I would like to do this with Google Finance only as nothing is as fast as google finance.

I hope this is possible.

Waiting for your replies.
Regards

Hi everyone,

I need to create a spreadsheet listing the current value of several public limited companies, i therefore need to pull information from balance sheets published online into Excel. I want to construct the spreadsheet in such a way that it updates itself when new reports are published so that it is always up to date.

I have managed to gather information such as the latest share price, market capital, 52 week high/low etc.. from the 'MSN moneycentral stock quotes' add-in, but do not know how to do a similar thing for balance sheet information.

I can't find balance sheet information on the companies that i am analysing on MSN money, but i can find it on Yahoo finance. Is there an Excel add-in that will allow me to pull this information in a similar way to the 'stock quotes' add-in? If not, is there any other way to do this?

Thanks in advance - any help or guidance will be much appreciated.