Excel 2010 :: Multiplying Price By Variable Number Of Years And Annual Compounding Interest To Price
Sep 5, 2012
I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.
Currently...
B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))
J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))
Example:
If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.
I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.
I'm trying to create a excel sheet which will automatically return a price based on a given width and drop value. Currently, I calculate the price manually by looking in a price book which has plenty of rows and columns and prices. I want to simplify this by simply entering the dimensions so it automatically calculates price based on the dimensions entered.
Below is the start of my worksheet. If I choose the exact sizes shown on the table, it will return a price, however if I choose a size that is not listed, I get an #N/A. Eg. If I choose 780 x 1500, it will return the price $179, but if I choose for instance 775 x 1490, it only returns #N/A, when I want it to still return the price $179.
My formula in K3 is =IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCh(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0)))
The analysis basically has 2 data components to it: The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time.
The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased.
What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below:
1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2)
2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet is the least)
3) Pull the price for this "closest quantity"
I have uploaded a worksheet showing the structure of that data. [url]
Is there some VB code I need to do this, or can it just be a few simple formulas?
All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current)
Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines Sheet2 (has current material codes plus current pricing), has about 36000 lines
I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,
I have have a large array of prices (across rows) and am looking for the closest price to match a price that I have been provided with. It's a basic benchmarking exercise on a row by row basis....and the price can be positive or negative. Is there a clean way to reference the closest price?
I have come across a fair amount of solutions, but none worked optimally - particularly the =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) approach....it just didn't work for some lines, and only worked for values less than source price in other instances.
I would also like to reference the source on the next column.
attached the spreadsheet that I need help with in that same thread. Please check out the spreadsheet via: [url]
Here's my issue:
I'm trying to be debt free roughly around 10-11 years years based on my current plan. SO, what I am trying to do is figure out what I can do with my savings at 1, 2, and a 3 % rate of return in a savings account that is compounded monthly after that debt free point. My time frame for results are 5-10-15-and 20 years.
Where: A23 = a date of installation I3 = TODAY() B23 = a number of years
It currently calculates correctly if the number of years correctly if it's older than 1 year. If under one year, it yeilds 0. I would like B23 to show 1 if the current formula yeilds 0.
I want it to yeild a 1 if the current calculation is 0.
I am trying to set up a budget for my self. Of course I am trying to make it as complicated as possible.
I have done searches online and that is how I found this forum. I am finding amortization formulas, but not what I want
Here is my question.
I have two fields, Mortgage and auto loan
I want to have a field that does a calculation for me. I know following fields as an example making numbers up Interest rate is fixed at 5.5% Amount left on loan is 150,000 Loan is fixed at 30 years with 28 years left
I want to run a formula so when I make a monthly payment of 1000 dollars how much of that goes to interest, how much to principle (not to make this complicated even more, but let say that I have 200 escrowed which I am not sure if that has interest calculated on(I don’t think so as it is property tax?))
Same example for car loan. 5 year loan, 4.5% interest, payment 438 a month.
If i borrow Rs.50000/- at 7.25% interest compounding monthly, repayable in 4 quarterly instalments, when i put it in excel using pmt function for emi quarterly payment and ipmt for interest calculation at the end of the 4th quarter (i.e last instalment) the balance will not become zero it shows a balance of Rs.14.35 its due to interest compounding monthly, is there any formula in excel to overcome it?
to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.
What I am trying to do is to create a formula for the attached spreadsheet - that calculates the daily compounding interest based on the higher rate of the two rates for the first five years then after 5 years the calculation should only be based solely on the blocked rate.
I have created a excel sheet here i want the total interest charged for three months in 3rd mnth interest charged column, if i select 7 mnths term total interest charged for 7 months should come in 7th month interest charged colum, if it is 13 months total interest for 12 months in 12th month interest column and remaining 1 month interest in 13th month interest charged column
attached is my spreadsheet. What I would like it to do is when someone marks a "N" or some other symbol in the box showing that supply not there; that excel copies the item #'s for the products listed and calculates up the prices for those items. Thank you in advance for your help. If at all possible if it could calculate the order on another sheet such as sheet 3 that would be fantastic
I am trying to do a vlookup to 1 sheet based on customer number and return price. If no customer number than I want to do a vlookup based on dunnage number to return the price. This is what I have for a formula but it never returns a value if one is found in the first lookup.
I'm trying to create an autoupdating price list using a part number and a website. I've tried Excel's data import wizard.
Website: WebFLIS - Public Search Sample data Category Chaplain Kits Item Chaplain's Kit, Consumable NIIN 9925-01-326-2855
Price $276.94 Description Also called the ReSuppply Kit
VBA Code that allows me to automatically open the page.
Dim IE As Object Sub submitFeedback3() Application.ScreenUpdating = False Set IE = CreateObject("InternetExplorer.Application")
[Code] ......
Ideally, I'd like to click a button that says "Update Prices" and it will search WebFlis for the NIIN listed and update the price. I have 717 items on my list so updating would be by click only (I think I can write that portion).
If that is not an option, I'd like to be able to click on each item row (think hyperlink) and be able to see the results for that individual item.
Currently I am using MS office 2000 premium. I would like to implement real-time stock prices (i.e. ticker MSFT) from the Nasdaq website (NASDAQ Stock Market - Stock Quotes - Stock Exchange News - NASDAQ.com) into a single cell, so that this cell shows the current stock price only.
By a click on the hyperlink symbol in the excel sheet I entered the following information... Microsoft Corporation (MSFT) Real-Time Stock Quote - NASDAQ.com and clicked the OK button.
Instead of receiving the real-time stock price information directly into the single cell, a new window opens with the Nasdaq website and all kinds of information that is not needed.
If I invested $350 per hectare into a project and at the end of 12 years that investment yielded me $150,000, what would my rate of return be over the life of the project?
I have a unit price and a quantity. I want to be able to take the sum of the extended price without having to add a column for extended price. I don't want to just hide it, either.
I have a checkbox where the values are reflected as True/False in a seperate column, what i simply want to do is take the value from one cell, and add 10% of the value each time a value becomes True.
For example in this value column after the check box has been filled out it might look like this:
False True True True False False False
The cell then needs to take the figure, lets say, 100, add 10%, then add 10% to 110, then again and so on for however many "True" statements are in the column.
I have tried with no success with various SUM/SUMIF/COUNT/COUNTIF/IF etc cant seem to get it to work, im not sure the cell refreshes correctly after the checkbox is filled out and its not registering the new "True" value as by default its all set to "False".
I am looking to calculate compound interest over a period of 10 years.
I am looking at putting a lump sum in at the beginning and contribute in monthly installments for the entire 10 years. How would I go about this.. I know there are formulas there, however I'm not a financial person at all..
I am using 2010 to write some code for a spreadsheet that will be used in 2007, so I know that RepeatLabels is not available.
My question is if you Group a date field by Months and Years and the data spans across 2 or more different years, is there a way to hide months in a specific year? I ask because the Date field has only 12 months in it and if I hide Jan (for example) it hides Jan in both years. (attached pic of filter for Date field below.)
Is there some other way to format it so it can discern between years or do I need to add yet another column to my original data?
how to calculate the time between two dates in years and quarters (represented in .25 increments). I would round down on the .25 increments (3 months) if not fully surpassed.
See attachment on what it should look like. C2 is my calculated column.
I am trying to find out how to set a formula that allows me to price point. Our marketing department want all product prices to be either to the value of 6 or 9
I have a task to rank the stocks from the highest price to the lowest price. I've made a template and managed to get the ranking right. I used the LARGE() function.
But now the difficulty is to get the 'Stock name' related to the ranked price. I may do it with the MATCH() function. But the problem is there is an equal number 15%.
My goal is a price code for cost of goods. In G1 I have "BLACKHORSE". I need a code will convert 1 to B, 2 to L, BLA for 123 and so on. This formula is giving me BBBLBB for 1. I don't need cents, just whole numbers.
i have a some detail in the tabular form with different criteria as size, colour ,purity,cut ,by combining all these i have price list in a tabular form , if i want to intersect all this and find the price how can i do so
