FV() Find the Future Value of Cash Today
         -Savings/Retirement Plan Calculations

A friend wants to find out the present value of an asset he owns that will generate $5million over next 25 years.
I am assuming that it will be at the rate of $200K p.a.
For a discount rate of 10% what is the formula to calculate the present price that he should accept.
Thanks
Pedro

Team,

More of a finance / how to question...


I need to calculate how much money needs to be allocated to an investment account so that some years in the future I'll have the required funds available, given a know investment rate..

E.g. I can invest my money at 5% per year. In 15 years, I need $5,000,000. How much can do I need to divert to the investment fund per month so I yield the required monies.

This would be the opposite of a loan payment.
I can do it on my finance calculator, Just don know how to do it in excel

Any suggestions?

Thanks,

John
In Annapolis.

I created a workbook for forecasting future cash flows:

I have a monthly cash flow projection worksheet that I need auto-filled. I need the cash flows to start at a given start date, then based on the duration input forecast cash over that given period. If the start date changes, I need the all the info to move with it.

Also the cash flows that will be filling these cells need to choose a set of percentages based on the duration chosen and these percentages will be multiplied by the contract amount and auto-filled into the correct place in the projection

I don't know where to start with my function/formula, I do not know VBA and would like a basic formula.

Any ideas would be wonderful

Hello,

Say I have a MAX function which selects the MAX between 10 variables which are calculated in VBA code.

Ex:

In Year 1, the max function looks at those 10 variables, selects the one with the highest value.

I want the code to then exclude that variable for future MAX calculations as in in Year 2 the MAX function would select the maximum value between the 9 variables left and so on.

How would go about doing this?

Thank you

Hi,

I'm new to excel and have been asked to model a rather complex investment structure. If anyone could help me with this I would be forever grateful. The cash flows are distributed between the limited partner (LP) and the managing partner (MP). There are three hurdles though with different divisions of cash for each. The structure is as follows

For cash flows generating an IRR less than 15%
LP gets 75%
MP gets 25%

For cash flows generating between IRR 15%-20%
LP gets 50%
MP gets 50%

For Revenue reaching over 20%
LP gets 25%
MP gets 75%

So I have my NPV cash flows laid out and have calculated the IRR for each year but can't visualize how to calculate the relevant sums of money for each cash flow. For example, the amount of cash that generates a 15% IRR in year 3 will be different than the amount of revenue that generates a 15% IRR in year 4.

So say there is cash flow for a year that generates a 30% IRR. I would need to first split the cash into three separate sums. The first would be the cash that generates an IRR of up to 15%, then another sum of the cash on top of that that generates between 15-20% IRR, then all the cash over 20%. The sum of these three chunks will equal the total cash flow for given year. Then I can apply the relevant structure to each cash sum (LP gets 75% of first sum, 50% of second sum, and 25% of third sum)

I need to split up the cash flows into the three different groups based on the hurdles so I can apply the correct structure to the relevant part of the cash flow.

Can I get excel to spit out the relevant sums of money for each hurdle level for each year?

Sorry if this is not clear, I am completely new to this and will elaborate if need be.

Thanks,

Colin

Hi

Can anyone tell me a formula/command to pick the next chronological date from a list (see below).

26/04/2010 17/05/2010 24/05/2010 31/05/2010 07/06/2010

example - today is the 20th April, so the answer i need is the 26th. When the 26th April passes, the next date will be the 17th May......and so on.

Thanks

I have a problem with the below code. I want the code to find any date in the selection that is a future date from the day they access it and change it to FUTURE DATE in red. This code does that but it also find anyother text that is not a future date and changes it too. Is there a way to just make it find the future dates and nothing else. Any suggestions?

Code:

Sub Macro1()
'Forces Cells in column E to not have a future date and creates warning message box if cell is empty or cell is filled with "FUTURE DATE"
Dim rng As Long, Rng1 As Range
rng = Sheets("Alpha").Range("A" & Rows.Count).End(xlUp).Row
   With Sheets("alpha")
       .Range("E3:E" & rng).Select
 End With
 Sheets("ALPHA").Activate
 For Each C In Selection
        If C.Value > Date Then
        MsgBox "Request Date in column ""E"" CANNOT be a future date", vbCritical
            C.Value = "FUTURE DATE"
            C.Font.ColorIndex = 3
            C.Font.Bold = True
            Cancel = True
        Else
            If C.Value = "FUTURE DATE" Then
            MsgBox """FUTURE DATE"" needs to be changed to actual request date in column ""E""", vbCritical
            Cancel = True
          End If
          End If
          
          Next C
End Sub

Hi,

I'm wondering how to write a formula that would calculate the number of days
between today and a future date. and when the date is reached the cell where the formula is applied will show the text Expired.

Lets say for example If I write 23/11/2011 in column H I want the formula to calculate the remaining days until the expiry date.

Any help on this would be kindly appreciated.

My date format is in dd/mm/yyyy.

I am having trouble figuring what function (calculation) to use.

First, an overview of why I need this. I run fun promotions in schools. When a child sells widgets in a fundraiser, I let them draw cash out of a cash bag as a reward. Here are the exact instructions to the students...

For every 3 widgets you sell in our fundraiser, I will let you draw 1 bill out of a cash bag. The cash bag will be filled with $1's, $5's, $10's and $20's. Example: If you sell 9 widgets, you get THREE pulls out of the cash bag (one bill per draw).

Given the TOTAL NUMER OF DRAWS to be done within a school, I want an Excel formula (or calculation) to tell me how many $1's, $5's, etc to put into the cash bag.

As a general rule I want more lower denominations (such as $1's) and fewer high denominations (such as $20's).

I want to spend an exact predetermined dollar amount TOTAL on this promotion.

For example:

GIVEN: I need 891 draws at a school.
GIVEN: I want to spend $2,219 total.

How many $20 bills should I put in the bag?
How many $10 bills should I put in the bag?
How many $5 bills should I put in the bag?
How many $1 bills should I put in the bag?

I know that there could be MANY answers to this question, depending on exactly how many of each denomination I wish- but I don't really care about the exact amount. I am content to let Excel determine that for me as long as there are MORE lower denominations (such as $1's) and FEWER high denominations (such as $20's).

My brain just isn't big enough to make Excel work for me on this. Is it even possible?