Does anyone know a formula that calculates the future value of an unequal payment stream? I am attempting to calculate the value of an investment plan where the periodic payment increases by 1% each period. I can get the answer by using the FV formula for each payment and then adding them up, but would like to be able to do it without having to do so.
View 9 Repliesfollows in paragraph 5 - but first, background!
I have a specific formula (received courtesy of some clever person here at Ozgrid (thanks!)) which I use to calculate the Future Value of a series of future payments that increase at a fixed annual rate and earn interest at a fixed rate.
Here it is: =Pmt1* SUMPRODUCT((1+Increase_in_payment)^(ROW( OFFSET($A$1,0,0,Term,1))-1),(1+Return_on_investment)^(Term-ROW(OFFSET($A$1,0,0,Term,1))+1))
(Example: $1000 per annum (Pmt1) is invested for 20 years (Term). The interest earned on the $1000 is 10% per annum (Return_on_investment). The $1000 increases by 5% (Increase_in_payment) each year - i.e. 19 increases - answer: $89,632 (rounded))
This formula assumes that the payment is made at the beginning of the period.
Question: I would like to change the formula to use MONTHLY payments made in advance, and interest earned on a monthly basis.
Because I REALLY do not know what the formula does, maybe I could ask for a detailed explanation thereof - maybe even from the person who supplied it to me (I cannot see who did!) - and then I can start fiddling with it myself if answers do not come.
Two previous posts of mine that dealt with somewhat different issues on the same formula are:
Determine Present Value From Future Value
and
[url]
First, ignore the colored portions. This is how I kept track of what I had completed in the form as I created it. Clients attend class once a week and pay once a week for their classes. I'm taking this one step at a time. At the initial interview of a particular client we will determine the payment per session due, and we will enter this amount in the column labeled "payment per session. We also need to keep track of how many sessions they have paid for. So in short, I'm looking for the following:
When "amount paid" equals "payment per session" set "minimum payment due" to $0 and increase "classes paid" by +1
I figured out how to enter the payment and return the minimum payment due to "0" but how can I make the "classes paid" increase by 1 every time the minimum payment equals "0"?
I'm trying to compare the 1st 7 days average this year vs the 1st 7 days average last year. How do I write the formula?
Last YearThis Year
536,212
852,865 417,290
345,981 702,816
850,560 189,808
335,285 691,152
572,165 283,036
842,145 404,916
735,364 528,332
121,475
348,793
697,944
876,360
671,170
928,103
I have 2 columns D, E and I would like to find cases in column E that are not equal. based on the numbers in D.
For instance, in D I have the numbers
4901780959501
4901780959501
4901780959501
4901780959501
4901780959501
in E respectively I have
MSX-M2GSX
MSX-M2GSX
#N/A
0
MSXL2GFSHX
Well how would I be able to spot these variations and insert all of them in columns G,H ?
i am a builder and i want to make a spread sheet to keep track of what i have been paid and the labour costs i have paid out
so i am working on 20 houses that all have 7 stages of payment each, and 5 to 8 men working on them who receive a price for each stage, but are paid a portion of that price each week while they are working on that stage
what i am trying to do is set up a sheet to keep track of what has been paid and the balance remaining for each stage and what has been received
i have tried doing it a few different ways but im none to clever with spread sheets and what is causing me the problem is next to each payment made i need to put the date and the name of the person who was paid so it always seems to just look a cluttered mess
have any of you got any ideas of how i could simplify this and make it look neat and easy
or alternatively is there some other software that may suit this application better?
I am wondering if anyone could possibly help me figure out an excel function that would allow me to make a list of payments by year (from 2008 to 2015) that are received on the second tuesday of every month.
View 9 Replies View RelatedI am attempting to program a column of cells so that if say "B3" doesn't match the contents of "A3", then "B3" will be highlighted in some way, maybe with a color. I'm using Excel 2000 and I can't see a way in Conditional Formatting to accomplish this without having to program each cell in column "B". I was hoping to be able to click and drag down in column "B".
View 9 Replies View RelatedColumns E and F can have a 1 or a Zero
If e2, d3, e4 = all 0's or all 1's, count = 3 in col F
If either e2, d3 or e4 is not equal the count is 2 in F
Example
D E F
0
0
0 3
0 1 2
1 1 2
1 1 3
Also, why would this formula work with numbers but not letters?
=IF(B2="x",0,IF(C2="x",1,""))
I am using this formula
=OFFSET(Inputs!$E$124,0,0,COUNTA(E124:E168),1)
to get an array with the names of some inputs excluding the blanks ... hit F9 to see that it works fine...
Now i want to combine the arrays produced by this formula for two different lists.
how to achieve that? please let me know if this is too vague..
I have some sales records that contain GBP payments, along with AUR, USD and EUR.
The GBP payments are not a problem, but a formula which could automatically convert AUR, USD and EUR into GBP payments would save a lot of time and reduce any chance of errors.
I have attached an example of the current layout.
Ultimately, I would like to put the sale in it's GBP amount where the boxes are blank & then a formula that detects either
A) If Original currency (the F column) is not GBP, then multiply the amount in the C column by whatever the exchange rate is in the G column.
B) If the exchange rate column G, has anything other than N/A, then multiply this by the amount in column C.
So, if the original currency in column F is in GBP, then I can enter data into the C column, and it will not change, however if the original currency in column F is not GBP, I would like the data in column C to change.
The example:
Coloumn A contains dates format of 12/02/2009, but another format such as 10-Apr-09 etc could be used.
Coloumn B contains the amounts of payments received, i.e £5.00, £10.00, £20.00
Now what I require is to be display in another coloumn (say Coloumn C) the number of payments that were received last week and last month and then the total value of the payments.
So the sort of result I'm looking for would be like
Assume todays date is 19-04-09
A B C
12-04-09 £5.00 Last Week 4 Payments Value £45.00
12-04-09 £10.00
13-04-09 £10.00
14-04-09 £20.00
how to plot dates which are unequal in interval
the dates that I want to plot are the following:
1/2/13
1/7/13
1/14/13
1/21/13
1/28/13
2/4/13
2/11/13
2/19/13
2/25/13
3/4/13
3/11/13
3/18/13
3/25/13
I want to appear them exactly on my X axis no different dates in between them.
using the ttest formula function
View 2 Replies View RelatedI have a problem finding the correct formula for counting matches with conditions between 2 non-equal ranges in Excel. The sheet is a try at making a working schedule template a bit automated.
For Week 1 each cell in the H16:H25 has a drop-down list (originating from BD30:BD50) where a work position can be chosen. The fixed list in BD30:BD50 starts with “<<SELECT>>” which is the default choice for the cells in H16:H25, and then “HOLD” before continuing with various work position names. K16:K25 is shift number 1 on Monday, L16:25 is shift 2 on Monday, and so on until Shift number 6. Then the rest of the days of the week follow (each with 6 shifts). Monday through Sunday (with 6 shifts for each) ranges over K16:AZ25. In the cells in K16:AZ25 the following can be entered: “x” (work), “o”(off), “-“ (leave).
The issue is the formula in each of the K26:AZ26 cells which are to total each of the shift columns . I want to count all the “x” in each column, but ONLY if the positions chosen in H16:H25 matches one of the positions in the list in BD30:BD50. NOT if a cell in H16:H25 displays “<<SELECT>>” or “HOLD” (even if it has a “x” entered in one of the Shift cells).
For example: .....
How can I search a range of cells that have a date entered by hand for a certain month and then sum the amounts given in another range of cells. For example, search several worksheets for payments made in September and then return the sum of those payments.
View 3 Replies View RelatedHow would I select a dynamic range in VBA without using name a range in Excel. I have searched for possible answers, however they use xldown, or xlup which works great for non-empty cells. My problem need to select the area where there are empty cells. Here is a snap shot of the data looks like:
Trade Date A B C D E
2008-Feb-11 450,432
2008-Feb-13
2008-Feb-15
The cell with number is where I got the formulas. Now i wish to fill that formula with the rest of the area. However, the problem is the number of rows and number of columns may change in the future. So I need to select those empty areas in a dynamic way.
Can periodical fixed deductions/payments be inserted automatically to the individual MONTH sheets of the workbook I uploaded ? To reduce the size of the file, I have left only ONE of the twelve month-sheets - the one for January.
Rephrased :
How to automate the entry of fixed amounts at regular intervals?
My guess is that from time to time, a macro would have to be run
- one of the macro's inputs would be the computer date and the other two inputs would be the value of the entry to be made and the value of the intervening period.
In practice there would be more than one regular entry and the values would be both positive ones and negative ones.
I guess that means one macro for each financial pattern.
Feasible or feesible (a multi-million-dollar-fee task)?
I need to create a sheet that will take payments away from a starting balance. The catch is that I don't know the starting balance. The interest is compounded annually at 3% and each withdrawl is different. My sheet needs to show the interest earned in each year and the account balance like a running sheet, after lets say 5 years there needs to be no money left.
View 2 Replies View RelatedI have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.
One method has been using this sumproduct formula:
I have uploaded a sample amortization schedule.
1. I require the table to adjust itself based on the loan period and number of payments per year entered in D14 and D15 respectively.
2. Also, if a value is entered in column E, then i require the whole table to update as well.
I have data similar as shown in the following:
A1A11
B2AA1122
C3B22
D4C33
D44
The idea is to add compare the cells of the first column with the third column. Where same letters/words exist, the corresponding value of the first column should be added to the second column (where no letter exists equally, the space remains empty), so it will look like this
A111
AA1122
B222
C333
D444
the third column always will have at least the same letters as the first column, but new letters/entries can occur.
I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.
Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.
I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
I have a table that includes 2007 data and 2008 data. When I was entering Jan 1 for 2008, it defaulted to 2007. I had the whole table completed with all the 2008 dates before I realized this. Is there a quick way to highlight all the 2008 data and change only the year?
View 9 Replies View RelatedI have a list of african countries and their C02 emissions from 1990 to 2010. The question I'm asked is, who will be the top 5 emitters in the year 2020 given the current trends. I have done a lookup command and compiled a list of the top 5 emitters. My concern is though i do not know how to get the 2020 forecast of the top 5 emitters rather than the current datas.
View 4 Replies View RelatedI am trying to find a formula which will show the date of a potential student on a certain date e.g 1st August 2014
Cell A2 has the students date of birth
Cell B2 has the students current age
I would like cell C2 to show the students date of birth on the 1st August 2014.
I think I may have over engineered this formula.
$F$40 = Cell with Event Date
=IF(WEEKDAY($F$40,2)
I want to have a future date (30 days from today’s date) added to a cell in column A only if that cell is already blank and column N has an “O” in that same row. Here is the code that I have right now, and it works to add a value based on the conditions that I just described. I just don’t know how to get it to add a date to that is 30 days in advance of today’s date.
If Me.Cells(.Row, "N").Value = "O" And Me.Cells(.Row, "A").Value = "" Then
Me.Cells(.Row, "A").Value = "1"
End If
I want to make a little chart for easy reference that tells me due dates for projects, based on estimated completion times.
I'm already using NETWORKDAYS to find the amount of working days between today and a due date, but I want to flip the formula around, and I'm having trouble getting the syntax right.
For example, column A reads:
0
1
2
3
5
7
10
15
20
Estimated completion times for various projects.
So I want column B to read the date that this would render. A1, value 0, would always produce today's date for B1. B2 would always read one business day into the future, B3 would read as 2 business days into the future, B4 as 3 business days into the future. Does that make sense, and B5 as 5 bd into future.
I need to predict sales data in future using multiple independent varaibles.I used FORECAST function to predict sales value for single independent varaiables.But i dont know how to predict sales using multiple varaiables.
View 10 Replies View Related