# Calculate Future Value Of Required Minimum Distributions For IRA

Jun 12, 2009
I've written the following code. It calculates the future value of required minimum distributions for traditional IRAs. When I enter the function in excel, it drops the last argument. I've tried changing the order of the arguments, and it always drops the last one. The arguments are : marginalTax (marginal tax rate), r (expected return on investment), t (time), begAge (beginning age), RDT (as range -- required distribution table; looks up the percentage required at each age), IRAValueat70 (calcualtes the value of the IRA at 70 years of age)

Function RMD(r As Double, marginalTax As Double, begAge As Integer, t As Integer, RDT As Range, IRAValueat70 As Long)

age = begAge

endAge = begAge + t

Dim holder As Long

Dim RMDnow As Long

Dim RMDall As Long

hat = 70

If endAge < 69 Then

Else

If begAge <= 69 Then

Do While hat < (endAge)

RMDnow = (IRAValue / (Application.VLookup(age, RDT, 2, False)))

RMDnow = (1 - marginalTax) * RMDnow...........................

View 2 Replies
ADVERTISEMENT
Jun 3, 2006

I have two data sets one measured and one modelled and I am testing a conceptual model that proposes that only where the tails of the probability distributions of each data set crosses will you experience an event. I'm enclosing an example file which contains both data sets and some descriptive statistics as well as the associated histograms and a diagram of what I'm ultimately trying to achieve. My question is how I go from the data sets I have with such a large discrepancy between them to plotting their probability distributions?

View 5 Replies
View Related
Oct 26, 2008

I use excel 2007 and have a s/s running to 350,000+ rows.

Data is in ranges of 2 to 30 rows with blank row between each range.

Column F numbers the rows in a range (if row 120,000 begins new range then F1200,000 = 1, F120001 = 2, etc).

If there are 2 rows of data in a range then, including blank row, there are 3 rows between this and next range.

What I want to achieve is a minimum of 6 rows between each range.

Does anyone know code that can can achieve this end?

View 3 Replies
View Related
Nov 9, 2011

I'm trying to calculate a date in the future and subtract one day.

The date entered in N38 is 11/09/11.

The term entered in months in C8 is 24.

I want N39 to return the date 24 months in the future minus one day.

So N39 should dispay 11/08/13

How the heck do I do that?

View 5 Replies
View Related
Jul 25, 2007

I am trying to create a spreadsheet that will automatically calculate a date in the future.

The future date calculated needs to take the time NOW (as the user completes a field) and determines if this is before MIDNIGHT on the following SUNDAY. If so it calculates the date as a Tuesday 2 weeks on. For example... I request a date now on 1st Jan which is a Thursday. This is before midnight Sunday and so the date calculated would be Tuesday 20th January.

If the time NOW is after MIDNIGHT Sunday and before the next TUESDAY.. ie 0001hrs on the Monday and 2359hrs on the Monday then the date returned should be Tuesday 3 weeks on.. e.g 27th.

It would also need a function that would allow the total number of referrals on a Tuesday to 20 at which point it would overspill and provide a date on the Friday of the same week.. so in the first example it would be Friday 23rd

Is this something that can be achieved with formulas and a set of dates for it to calculate from or do I need to find someone who can do it in VBA for me.

View 9 Replies
View Related
Dec 29, 2013

I am working on a sheet right now where I want to put the day a certain number of days in the future but I want to skip only Holidays, or days in a list on another part of the sheet, but count the weekend days.

All the formulas and post I have read is about someone wanting to skip weekends too. I did find a workdays formula where you and pick what you want to be considered as the weekend but I just want to skip holidays.

Example:

Day is Dec-31-2013 7 days in the future is Jan-7-2014

I want it to be since Jan 1 is a Holiday the answer will be Jan-8-2014

View 4 Replies
View Related
Aug 25, 2007

I am using the same structure to update another column, under the same sub:

If Target.Column = 5 Then '

Select Case Range("d69").Value

Case "CB1W"

ActiveCell.Value = DateAdd("ww", 1, Now())

Case "CB2W"

ActiveCell.Value = DateAdd("ww", 2, Now())

Case "CB3W"

ActiveCell.Value = DateAdd("d", 21, Now())

Case "CB4W"

ActiveCell.Value = DateAdd("d", 28, Now())

Case "CB5W"

ActiveCell.Value = DateAdd("d", 35, Now())

Case "NI"

ActiveCell.Value = ""

Case Else

ActiveCell.Value = ""

End Select

I would like to apply the code to the whole column but if I change Select Case Range("d69").Value to Select Case Range("d2:1000").Value I get a Type Mismatch Error (13) - (on Case "CB1W" ) - column d cells are data validation drop down boxes that get their value form a named ranged :Lead_Status. The column is also formatted as Text. (I applied the code to d69 because I have data already in the other cells which I don't want altered till I have the code working)

I would also greatly appreciate your advice on how to incorporate the WEEKDAY function to exclude weekends for the DateAdd function.

View 9 Replies
View Related
Dec 27, 2007

I want to calculate a date that is 28 days in the future. I don't want to exclude any days - However - if the end date falls on a weekend or holiday, I would like to push it out to the next business day.

I currently have the weekends covered, but am stumped on the holidays.

(For weekends, I am using the WEEKDAY function on a hidden sheet, and then the following 3 IF statements:

IF today + 28 = Mon.-Fri., then give me today + 28.

IF today + 28 = Sat., then give me today + 30.

IF today + 28=Sun., then give me today + 29.

I have tried adding an additional IF statement to address a specific holiday - namely, President's Day on 2/18/08, which is a Monday - but it won't add the extra day, because I think my initial IF statement re: Monday being today + 28 is overriding it.

View 9 Replies
View Related
Sep 16, 2009

I'm trying to combine monthly calculations with "today" and with "workdays"

Example:

start date = 01/01/2009

today's date 09/16/2009

formula result = 10/01/2009 ; or if 10/01/2009 is a Sunday, result = 09/29/2009 (not 02/01/2009, 03/01/2009, etc)

=edate gives me a month but it doesn't skip weekends or calculate beyond today's date

View 14 Replies
View Related
Sep 16, 2009

Sometimes my due dates need to be on the 15th of the month, for which DLL and Donkey gave me: =DATE(YEAR(TODAY()), MONTH(TODAY()) + (DAY(TODAY()) > 15), 15)

This works great except sometimes the 15th is on a Sunday which means I need the result to be the 13th, and sometimes it's on a Saturday which means I need the result to be the 14th. FYI: I am a n00b..

View 2 Replies
View Related
Mar 23, 2007

I am trying to put together a spreadhseet that will calculate the future cost of education fee assuming a given inflation rate. I would like the reuslt to be highlihted in a list or a table. The user would only need input current fee, inflation rate and years where fees are payable. The spreadsheet would do the rest. I have laid this our best I can attached.

View 5 Replies
View Related
Sep 19, 2007

follows 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]

View 9 Replies
View Related
Sep 11, 2009

I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.

For example:

I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is.

I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated.

I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).

View 14 Replies
View Related
Aug 14, 2007

what I'm trying to do.

Background:

I play a game that requires 5 members to play, however you can have more then 5 members on the team. In order for a player to receive points for playing they must have played at least 30% of the total games played. The problem for me is when trying to figure out how many games someone needs to play to get to 30%, the total goes up as they play those games.

What I would like to accomplish:

I'm looking for help on a formula that would figure out how many games a player would need to play to get to 30%. I have a column that is total games played, %of games played by player, games played by player, and games needed to play to get to 30%. Basically the user would enter the # of games played by each player, and the total games played overall for the team for that week, the spreadsheet would then show the # of games each player that isn't above 30% would need to play.

I tried to make the question as clear as possible, if anyone needs clarification,

Originally Posted by shg

Welcome to Oz, Basca

spreadsheet with a representative sample of your data?

Games Played

Player 1 20

Player 2 7

Player 3 13

Player 4 20

Player 5 13

Player 6 13

Player 7 7

Player 8 7

Total Games Played20

View 9 Replies
View Related
May 21, 2009

I want to save one million dollars in 10 years earning 8.00% interest. How do I calculate how much I need to save per month.

View 11 Replies
View Related
Apr 23, 2009

Is there a easy way to calculate the MAX or MIN of column B dates to column A items that have say many different dates in column B? see attached excel file to show what I am looking for. How to calculate the MIN value in column "B" of the items in column "A" with formula in column "C"...

View 2 Replies
View Related
Jan 1, 2008

Trying to create a Math formula that allows for Taking advantage of sale pricing.

In Excel the following:

Qty to Order (G3), Std Package(I3), Price per Unit(J3), Sale Price(K3), =

Extended Price(L3)

Example

2case X 40peices per case,1.00Reg Price,.90Sale Price = 72.00

Not quite sure how to handle the Sale price taking over regular pricing

View 9 Replies
View Related
Jun 27, 2007

Attached is the zip file with the detail of what I am hoping to achieve which is calculating the number of lengths required from generated infromation.

View 9 Replies
View Related
Apr 1, 2009

A1, B1, C1 and D4 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively.

A1 B1 C1 D4

2 4 6 C1-Formula is: =sum(A1+B1)

Now I have to write formula for D4-cell:

i.e. =sum(C1/Total days remaining of the month)

Suppose Today is April 01, 2009, C1/should be devided by 30,

Tomorrow will be April 02, 2009, C1/should be devided by 29.

View 10 Replies
View Related
Mar 28, 2012

I am trying to create a formula to calculate the total number of calls that I need to receive to hit the target % if the current % is less than the target %. I have tried various formulae, but not having much luck. Also, it shouldn't show me any negative figures.

A = Departments

B = Calls Dropped

C = Calls Received

D = Current % (C/F)

E = Target %

F = Total (B + C)

G = Required (When I enter B, C and E, the rest needs to calculated automatically. G should tell me the amount of calls I need to receive in order to achieve the target specified in E)

Departments

Calls DroppedCalls ReceivedCurrent %Target %TotalCalls Required to hit TargetDept 1

21083.33%95.00%12 Dept 2

067100.00%90.00%67 Total

27791.67%95.00%79

View 7 Replies
View Related
Sep 24, 2012

i am trying to work with a formula that will look at date today (NOW) and compare this to a due date and in return provide me with only the working hours total. Working hours are 8am to 4pm (8 HOURS).If the due date is passed this will be a negative figure.

View 9 Replies
View Related
Mar 11, 2014

I am using excel to do a monte carlo analysis of a process. Until now, all my distributions have been uncorrelated, but from now I would like to produce distributions with a given correlation. This is a fairly easy process for normal distribution, but in this case one of the distributions is Weibull and the other lognormal. I don't have a stats background, so how to implement this. Producing the two distributions is no problem, but I would now like to be able to alter them or the original formulae so they are correlated properly. If it would greatly simplify the process, the lognormal distribution could be replaced by a normal, but not the weibull.

The formulae I'm using for each is:

View 4 Replies
View Related
Aug 2, 2009

I'm creating an (English) football predictions competition for me and my family.

One problem that has stumped me is how to get the scores based on the 'home' & 'away' score predictions.

The rules are: If I predict the correct exact result I get 3 points. I want to add another 'rule' whereby if I predict the correct winner, I get 1 point. Incorrect predictions get 0 points. I don't know how to do this using a formula.

View 11 Replies
View Related
May 1, 2014

I am trying to automate a process involving a Grubb's outlier test and calculating averages/stdev of a set of numbers. I have the data in one column and have calculated the average/stdev/%CV of that data. I have also added a formula to calculate Max/Min outliers of that column of data. Then there are cells with IF statements that display either "yes" (for an outlier present), or "no" (if no outlier is present). What I would like to do, if it is feasible, would be to set up formulas to recalculate the average/stdev of the column, taking into consideration either the presence or absence of outliers.

Here is what I invision(cells are for example purposes)

=IF((M9 = yes, Avg(H2:H40)-max(H2:H40)),M9 = no, avg(H2:H40))

So it would calculate the average without the outlier if the outlier "yes" was present, or just calculate the average if "no" is present.

I would also do this with a minimum outlier as well, but i can set that up if this first one is possible.

View 8 Replies
View Related
Apr 23, 2009

Iam studying civil engineering and iam trying to make an excel sheet to calculate reinforcement required for solid slabs

to calculate the reinforcment first you should calculate positive and negative moments along short and long directions with dead and live loads

what iam trying to do is creating 2 lists the first list is to choose the RATIO of Short Span(a) divided by Long Span(b)

The Second List is to choose a case out of 9 cases of possible connections

the idea is to choose a ratio from list 1 and a case from list 2 to return the values of crossponding moments

The table is attached

View 2 Replies
View Related
Jan 6, 2013

I have series of data values like below. I have to find Maximum, Minimum values for each of these values.

9430

9822

10070

[Code].....

View 2 Replies
View Related
Jan 4, 2010

Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.

View 3 Replies
View Related
Dec 24, 2009

I want in A1 to find minimum SUM if no minimum number in row.

Here is example attached: ...

View 9 Replies
View Related
May 19, 2007

I have had a lot of luck finding what I need from the search areas, and I even found some information on the formula I am trying to build. The problem is I don't understand it and I need some help. First let me set it up for you. (I do not know the formula)

If cell L125 is has a value >0.00, I need to locate the smallest value the range of cells C125:F125, I then need to subtract L125 from that number, otherwise enter nothing.

This really has me baffled. I tried and I tried but it will not find the smallest value then subtract L125.

View 9 Replies
View Related
Nov 14, 2007

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 Related