Valuation Spreadsheet
Yes, all of that is possible with google sheets... In general spreadsheets can do all of what you ask. Have a try of some online training like these to get started.
**Volume-weighted average price**  In finance, volume-weighted average price (VWAP) is the ratio of the value traded to total volume traded over a particular time horizon (usually one day). It is a measure of the average price at which a stock is traded over the trading horizon.  VWAP is often used as a trading benchmark by investors who aim to be as passive as possible in their execution. Many pension funds, and some mutual funds, fall into this category.  ***  ^[ [^PM](https://www.reddit.com/message/compose?to=kittens_from_space) ^| [^Exclude ^me](https://reddit.com/message/compose?to=WikiTextBot&amp;message=Excludeme&amp;subject=Excludeme) ^| [^Exclude ^from ^subreddit](https://np.reddit.com/r/finance/about/banned) ^| [^FAQ ^/ ^Information](https://np.reddit.com/r/WikiTextBot/wiki/index) ^| [^Source](https://github.com/kittenswolf/WikiTextBot)   ^] ^Downvote ^to ^remove ^| ^v0.24 "I could not figure out a good way to make XIRR work since it does not support arrays.  However, I think the following should work for you: Insert a column at D and call it ""ratio"" (to be used to calculate your answer in column E).  Use the following equation for D3: =1+(C3-B3-C2)/C2 Drag that down to fill in the column. Set E3 to: =(PRODUCT(D$3:D3)-1)*365/(A3-A$2) Drag that down to fill in the column. Column E is now your annual rate of return." "A bit strange but okay.  The way I would think about this is again that you need to determine for what purpose you're computing this, in much the same way you would if you were to build out the model. The IPO valuation is not going to be relevant to the accretion/dilution analysis unless you're trying to determine whether the transaction was net accretive at exit. But that's a weird analysis to do. For longer holding periods like that you're more likely to look at IRR, not EPS. EPS is something investors look at over the short to medium term to get a sense of whether the company is making good acquisition decisions. And to do that short-to-medium term analysis, they look at earnings.   Damodaran would say this is a shitty way of looking at things and that you should probably be looking at some measure of ROIC instead, and I tend to agree, but I don't get paid to think like an investor, I get paid to sell shit to them (if only in indirect fashion). The short answer to your question is that no, you should not incorporate what you are calling liquidation value when determining accretion/dilution, but only because the market typically computes accretion/dilution on a 3-year basis tops. I've never put together a book or seen a press release in my admittedly short time in finance that says ""the transaction is estimated to be X% accretive within 4 years"" - that just seems like an absurd timeline.  Final point is just that from an accounting perspective, a gain on a sale of an asset is not going to get booked in either EBITDA or OCF, so just mechanically there's no way for the IPO value to flow into your accretion/dilution analysis there, even if you are looking at EBITDA/shares. You could figure the gain on sale into some kind of adjusted EBITDA/shares version of EPS, but this is neither something I've ever seen nor something that really makes sense in the context of using EPS as a standardized metric across the market. Typically we take OUT non-recurring shit in EPS, we don't add it in. Adding something like this in would be much more appropriate to measuring the success of an acquisition/investing vehicle like a private equity fund, not a standalone operating company that reports operational earnings in addition to cash flow from investing. And as I suggest above, that's an analysis for which the IRR metric is more ideally situated.  And just a semantic thing - we typically wouldn't call the exit value a ""liquidation value"". That term is usually reserved for dissolution of a corporate entity and selling off its physical or intangible assets in piecemeal fashion (i.e. not accounting for operational synergies across the business). IPO value is actually just going to be a measure of market value of equity." I appreciate it. I didn't operate under selling the asset year five but other than that I followed this example. I appreciate the help. These assignments are just poorly laid out. Financial management also plays on different calculation interactions  so it is difficult for me to easily identify the intent at times. Thanks again. "By the sounds of things, you're not asking for a single formula but how to do the analysis... And for the record you're focusing on the wrong thing. You should be focusing on how much it costs to own your car during that time period, not your total equity.  Formulas:  I'm not sure how well you understand the nuts and bolts of the finance behind your question, (you may just be a pro and really want a consolidated equation to do this in one go.) So at the risk of over-specifying, I'll err on the side of starting at the very beginning. Any financial loan analysis is built on 5 items: (1) # of periods, (2) Present Value, (3) Future Value, (4) Payments, and (5) interest rate.  These are usually referred to in spreadsheet software as NPER, PV, FV, PMT, and Rate. Each one has its own Excel/google docs function where you can calculate one as a function of the other 4. I'll use those going forward and spare you the 'real math' equations.  Layout: If I were trying to solve your problem I would start by setting up the spreadsheet  up with column A as ""Period"". I would put this label in cell A2 and then starting from cell A3 as ""0"" and going to ""N"". 5 year loans will give you the highest purchase value w lowest payments, so n=60 months... but you also said 48 months so do whatever you want.  Then I would set up two tables side-by-side with 7 columns each. (Yes, seven.) Starting in C2, label the cells/columns as: ""Rate"", ""Car Value"", ""Loan Balance"", ""Payment"", ""Paid to Interest"", ""Principal"", and ""Accumulated Equity"". Then select and copy cells C2:I2 as the next set of column headers beginning in K2. (I usually skip a column to leave space because I'm OCD like that :) ) Numbers: Now you need to set up your initial set of numbers for each table. We'll do the older car in the left hand table and the newer one on the right. Let's say your rate is 5% APR. Put that in cell C1 (not C3). Then in cell C3 type =C$1/12. Car Value $12,000 in Cell D3. Then type ""Down Payment"" in cell E1 and put 10% in cell D1. And last, in cell E3 put the formula =D3*(1-D$1). This should leave you with a value for the first month in the Rate, Car Value, and Loan Balance columns. Now select C1:E3 and paste those to the right hand table. The only thing you will need to change is the ""Car Value"" to $20,000. As a check, you should have .0042 / 12,000 / 10,800 on the left and then .0042 / 20,000 / 18,000 on the right.  Formulas again: This is where spreadsheets become amazing. If we set up the right formulas, you can copy and paste them and do this very complicated analysis very quickly.  Payment The excel formula for Payment is =PMT(Rate, NPER, PV, FV). FV is usually zero. So in cell F3, type the formula =PMT(C3, 60, E3, 0). Obviously if you're really doing a 48 month (4 year) loan then you'll need to change the 60 to 48. You should be able to copy the result from cell F3 to N3 and the formula will update itself.  For the 60 months, I'm showing the 12K car/10.8K loan has a pmt of $203.81. The 20K/18K loan has a pmt of 339.68.  Interest The easiest way to calculate the interest is as =E3*C3. That's (Outstanding Loan Balance) x (Periodic Interest Rate). Put this in cell G4, since you don't actually owe any interest at Period 0.  Principal If you pay PMT each month and X goes to interest, then the amount to principal is ""PMT - X"". So in H4 type =-F3 - G3. The 'minus' in front of F3 is because excel's PMT function returns a negative amount. If you want to, feel free to type ""=-PMT(...)"" for the formula that's actually in F3. It's your call.  I get 159 for the amount to principal in period 1.  Accumulated Equity As I mentioned in the comment, your ""Equity"" comes from your initial Loan-to-Value and the accumulated principal payments. So the formula in this cell should reflect that. There are a variety of ways to do this... the easiest is just to compare your car's expected value to your loan balance every time. In cell I3, type =(D3-E3). That's your initial equity in the car before making any payments. Copy that cell and paste it to I4. You'll see it updates to =(D4-E3) automatically. (Right now that is zero... those cells are empty, but we're getting there) The important thing is that as JB King pointed out, your equity is a function of accumulated principal AND equity, which depreciates. This approach handles those both.  Finishing up the copy-and-paste formulas I know this is long, but we're almost done.  Rate // Period 1 In cell C4 type =C3.  Payment // Period 1 In cell F4 type =F3.  Loan Balance // Period 1 In cell E4 type =E3-H4. Your loan balance at the end of period is reduced by the principal you paid. I get 10,641.  Car Value // Period 1 This will vary depending on how you want to handle depreciation. If you ignore it, you're making a major error and it's not worth doing this entire analysis...  just buy the prettiest car and move on with life. But you also don't have to get it scientifically accurate. Go to someplace like edmunds.com and look up a ballpark. I'm using 4% depreciation per year for the old (12K) car and 7% for the newer car. However, I pulled those out of my ass so figure out what's a better ballpark.  In G1 type ""Depreciation"" and then put 4% in H1. In O1 type ""Depreciation"" and then 7% in P1. Now, in cell D4, put the formula =D3 * (1-(H$1/12)).  Paste formulas to flesh out table As a check, your row 4 should read 1 / .0042 / 11,960 / 10,641 / 203.81 / 45 / 159 / 1,319. If so, you're great. Copy cells C4:I4 and paste them into K4:Q4.  These will update to be .0042 / 19,883 / 17,735 / 339.68 / 75 / 265 / 2,148.  If you've got that, then copy C4:Q4 and paste it to C5:C63. You've built a full amortization table for your two hypothetical loans. Congratulations.  Making your decision I'm not going to tell you what to decide, but I'll give you a better idea of what to look at. I would personally make the decision based on total cost to own during that time period, plus a bit of ""x-factor"" for which car I really liked.  Look at Period 24, in columns I and Q. These are your 'equities' in each car. If you built the sheet using my made-up numbers, then you get ""Old Car Equity"" as 4,276. ""New Car Equity"" is 6,046. If you're only looking at most equity, you might make a poor financial decision.  The real value you should consider is the cost to own the car (not necessarily operate it) during that time... Total Cost = (Ending Equity) - (Payment x 24) - (Upfront Cash).  For your 'old' car, that's (4,276) - (203.81 * 24) - (1,200) = -1,815.75 For the 'new' car, that's (6,046) - (339.68 * 24) - (2,000) = -4,106.07.  Is one good or bad? Up to you to decide. There are excel formulas like ""CUMPRINC"" that can consolidate some of the table mechanics, but I assumed that if you're here asking you would have gotten stuck running some of those.  Here's the spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0Ah0weE0QX65vdHpCNVpwUzlfYjlTY2VrNllXOS1CWUE#gid=1" Thank you very much for this thoughtful response.  In my opinion the judges care more about the why behind your valuation rather than a how.  Anyone can use a formula, but it takes so much more to understand why to use the formula.    Personally, the 'why' is going to be the toughest part for me understand and wrap my head around.  Once again thank you for the advice and the tip. "The Securities Industry and Financial Markets Association (SIFMA) publishes these and other relevant data on their Statistics page, in the ""Treasury & Agency"" section. The volume spreadsheet contains annual and monthly data with bins for varying maturities. These data only go back as far as January 2001 (in most cases). SIFMA also publishes treasury issuances with monthly data for bills, notes, bonds, etc. going back as far as January 1980. Most of this information comes from the Daily Treasury Statements, so that's another source of specific information that you could aggregate yourself. Somewhere I have a parser for the historical data (since the Treasury doesn't provide it directly; it's only available as daily text files). I'll post it if I can find it. It's buried somewhere at home, I think." "I keep spreadsheets that verify each $ distribution versus the rate times number of shares owned. For mutual funds, I would use Yahoo's historical data, but sometimes shows up late (a few days, a week?) and it isn't always quite accurate enough. A while back I discovered that MSN had excellent data when using their market price chart with dividends ""turned on,"" HOWEVER very recently they have revamped their site and the trusty URLs I have previously used no longer work AND after considerable browsing, I can no longer find this level of detail anywhere on their site !=( Happily, the note above led me to the Google business site, and it looks like I am ""back in business""... THANKS!" "the ""how"" all depends on your level of computer savvy. Are you an Excel spreadsheet user or can you write in programming languages such as python? Either approach have math functions that make the calculation of ROI and Volatility trivial. If you're a python coder, then look up ""pandas"" (http://pandas.pydata.org/) - it handles a lot of the book-keeping and downloading of end of day equities data. With a dozen lines of code, you can compute ROI and volatility." That is called a 'volume chart'. There are many interactive charts available for the purpose. Here is clear example.  (just for demonstration but this is for India only) 1) Yahoo Finance 2) Google Finance 3) And many more Usually, the stock volume density is presented together (below it) with normal price vs time chart. Note: There is a friendly site about topics like this. Quant.stackexchange.com. Think of checking it out. You could create your own spreadsheet of Cash Flows and use the XIRR function in Excel: The formula is: I'm responsible for all our hedging.  Since we sell the energy to end users we do mostly fixed buys, swaps and calls.  I'm a excel guru and dabble a little in SQL.  we have Crystal Ball as well but i have no idea how to use it.  I guess I'm trying to figure out if there is a tool that people use to help me analyze the spreads. or perhaps some reading material to help me through this. This is what i've been working towards for so long and i really don't want to fuck this up "A (very) simplified bond-pricing equation goes thus: Fair_Price: {Face_Value * (1 + Interest - Expected_Market_Return) ^ (Years_To_Maturity)} * P(Company_Will_Default_Before_Maturity) To reiterate, that is a very simplified model. But it allows us to demonstrate the 3 key factors that drive ""Fair"" Value: The interest relative to the current market rate. If your AAA bond yields 1%, but an equally-good AAA bond currently sells at 3% in the market, then the ""Equivalent"" value is the face value minus 2% (1% - 3%) for every year to maturity.  Years to maturity. Because 1) is multiplied for every year to maturity, longer-dated bonds are more sensitive to changes in market rates. If your bond yields 2% less than market but matures in a year, then it's worth $98, but if it matures in 56 years, then it's only worth 0.98^56 = $32. Conversely, if your bond yields more than the market rate, then its' price will be greater than face value. The company might default on the debt. If a Bond has a ""Fair"" Value of $100, but you think there's a 50% chance that the company will default, then it's only worth $50. In fact, it can be worth even less because getting paid on a defaulted bond can often take time and/or money and/or lawyers. In your case, because your bond matures in 56 years but yields ~5% (well above the current market rate), for it to be below Face value implies a strong probability of default, or a strong belief that market returns will be above 5% over the next 56 years." It is meant to be a Valuation Model. So the data I have collected are for the Addressable Markets in potential countries( i.e. population currently affected by the disease and potential growth of the disease in the future). Possible competition for the drug. And most importantly the expenses of the drug (how much it cost to create the drug and clinical trails etc.) I need to create a Revenue chart and the Pricing for the drug as well. What I am looking for is someone who is familiar with creating a valuation model or has a template and can give me advice on how to structure this. thanks. If annualized rate of return is what you are looking for, using a tool would make it a lot easier.   In the post I've also explained how to use the spreadsheet.   Hope this helps.