[NA] Excel Formula Help : Financial

[AggieMike88]

I've RTFM'd this many ways and still not quite getting the answer or help in a way I can understand. And since a few folk here had other good answers to Excel questions, I turn to you guys once again.

I want to perform a time value of money calculation to show me present value when employing compound interest. I have two columns, representing the date of the "deposit" and the amount of the "deposit" into an account.

If it helps with your demonstration, let's go with the following data set and assumptions.

Interest = 6% APR, compounded monthly

DATE:....................Amount $
06/01/12................$1850.00
06/16/12................$1850.00
07/01/12................$1850.00
07/16/12................$1850.00
08/01/12................$1850.00
08/16/12................$1650.00 <<< note amount change
09/01/12................$1650.00
09/16/12................$1650.00
10/01/12................$1650.00
10/16/12................$1950.00 <<< note amount change
11/01/12................$1950.00

Straight sum of the amounts is $19,750. But if I'm trying to determine PV when the compound interest is figured in.

Figure the dates to be in column A and the values in Column B. What is the correct function set up to achieve the desired result. (write it out if possible so I can copy/pate)

 

[Clark1961]

Have you looked at the present value function?

Use it for each individual value rather than a stream of cash flows. In other words do a series of PV calcs, one for each date which takes the cash at that date to time zero. Once all values are at the same time then compute the simple summation.

 

[wabower]

All of the HP's will calculate NPV using an annual stream of different amounts. I assume excel has a similar function but don't have access from the ipad.

Have you looked at the present value function?

Use it for each individual value rather than a stream of cash flows. In other words do a series of PV calcs, one for each date which takes the cash at that date to time zero. Once all values are at the same time then compute the simple summation.

 

[Clark1961]

All of the HP's will calculate NPV using an annual stream of different amounts. I assume excel has a similar function but don't have access from the ipad.

You are undoubtedly correct. I was taught never to trust spreadsheet functions and do it all using my own formulas. Suggesting the present value function was sort of a compromise.

I can scan and send the formulas to the Aggie if need be...

 

[wabower]

Don't draw any buttons on the email or he will try to press them.

You are undoubtedly correct. I was taught never to trust spreadsheet functions and do it all using my own formulas. Suggesting the present value function was sort of a compromise.

I can scan and send the formulas to the Aggie if need be...

 

[Steve Foley]

I don't know how to do it using the PV function.

I did this:

I put the beginning date in A1 and the rest of the table as show into A2 to B12

My formula for C2 is: =(1+.06/12)^((A2-A$1)/30)

Giving me the interest rate accumulated on that payment. Divide that payment (B2) by that rate (C2) and you get the present value of that payment. (Formula for D2 is: =B2/C2)

Total up C2 for the present value as of date contained in A1.

There could be slightly different answers. I used 30 days per month, which I believe is standard for "Compounded monthly" - but I'm not sure.

 

[eetrojan]

I want to perform a time value of money calculation to show me present value when employing compound interest. I have two columns, representing the date of the "deposit" and the amount of the "deposit" into an account.

It's been a long time, but I'll take a stab at it. Your dates suggest a bi-monthly payment stream, but I think you can use the PV function provided that your interest is compounding every day (or providing that you're willing to assume that for the purposes of the calculation). Simply add a third column C and then put the PV function in each cell to figure out the present value of each cash flow, then sum them up, something like this.

The formula from my test sheet is =-PV($B$5/365,A10-$B$4,,B10)

(Note that I manually added the $ prefixes to some of the references in the first cell (e.g. $B$5 rather than B5) so that they wouldn't change when I copy and pasted the formula into the other cells):

 

[FlyerQ]

I don't know how to do it using the PV function.

I did this:

I put the beginning date in A1 and the rest of the table as show into A2 to B12

My formula for C2 is: =(1+.06/12)^((A2-A$1)/30)

Giving me the interest rate accumulated on that payment. Divide that payment (B2) by that rate (C2) and you get the present value of that payment. (Formula for D2 is: =B2/C2)

Total up C2 for the present value as of date contained in A1.

There could be slightly different answers. I used 30 days per month, which I believe is standard for "Compounded monthly" - but I'm not sure.

Or add a column of integers as your first column, so it goes 1,2,3,4,5 etc.

Then column C becomes

= B1 / power(1+(.06/24), A1)

Where B1 is the payment and A1 is the month number (i.e 1,2,3,4)

Then sum column C

 

[AggieMike88]

Thanks guys, I'll play with some of these answers to see which accomplishes what I am seeking.

I might have left off that I'm working toward the end that money is owed to me and I'm trying to add compounding interest to what is owed.

So I might be asking for the wrong item. Perhaps it's future value?

 

[Steve Foley]

= B1 / power(1+(.06/24), A1)

I think that's compounding interest bi-monthly.

How about

= B1 / power(1+(.06/12), A1/2)

That should change the answer by at least $0.0000003

 

[AggieMike88]

Don't draw any buttons on the email or he will try to press them.

 

[eetrojan]

Hi Mike -

I was a little confused by your use of the phrase "future value" because you cash flows were in the past. However, that's still sort of confusing even if you're talking about getting repaid.

Are you saying you loaned $19,750 to somebody and you want to know how much you should be paid on certain dates in order to get your $19,750 back, and also earn 6%?

 

[FlyerQ]

I think that's compounding interest bi-monthly.

How about

= B1 / power(1+(.06/12), A1/2)

That should change the answer by at least $0.0000003

Quite true... Posted it as a monthly solution not noting their was payment mid-month and then just edited a quick hack.

 

[FlyerQ]

Thanks guys, I'll play with some of these answers to see which accomplishes what I am seeking.

I might have left off that I'm working toward the end that money is owed to me and I'm trying to add compounding interest to what is owed.

So I might be asking for the wrong item. Perhaps it's future value?

Suppose there is a stream of 12 monthly payments of $100 at 6% interest.

Present Value - you will receive this stream and want to know what it is worth now. Since you receive a total of $1200 ($100 each month) and expect to earn 6% you need to pay less than $1200 to own that stream. In this example the PV is $1,162.

Future Value - you will deposit this stream and want to know what it is worth after the last payment. Since you will pay a total of $1200 and expect to get 6% interest it needs to be worth more than $1200 in one year. In this case the FV is $1,234.

Not sure this helps.

 

[AggieMike88]

Are you saying you loaned $19,750 to somebody and you want to know how much you should be paid on certain dates in order to get your $19,750 back, and also earn 6%?

I'm not willing to reveal the details of the transaction. But flip this to say I invested money into a family entity on a particular schedule and I'm trying to determine what my "cash out" number needs to be.

Sample numbers are not representative of the transaction, just something to use for you guys to help with explaining what math formula to use.


And please, keep any comments no related to my original "what is the formula" question to yourselves.

 

[AggieMike88]

Suppose there is a stream of 12 monthly payments of $100 at 6% interest.

Present Value - you will receive this stream and want to know what it is worth now. Since you receive a total of $1200 ($100 each month) and expect to earn 6% you need to pay less than $1200 to own that stream. In this example the PV is $1,162.

Future Value - you will deposit this stream and want to know what it is worth after the last payment. Since you will pay a total of $1200 and expect to get 6% interest it needs to be worth more than $1200 in one year. In this case the FV is $1,234.

Not sure this helps.

By your explanation, FV might work since, even thought were at the end of the timeline now, I can always view the result as if I was at the beginning.

 

[Sam D]

Thanks guys, I'll play with some of these answers to see which accomplishes what I am seeking.

I might have left off that I'm working toward the end that money is owed to me and I'm trying to add compounding interest to what is owed.

So I might be asking for the wrong item. Perhaps it's future value?

So, $19,750 is the principal which is paid off from June - November, but there is also an interest component the needs to be paid off as well?

 

[AggieMike88]

So, $19,750 is the principal which is paid off from June - November, but there is also an interest component the needs to be paid off as well?

In my example, $19,750 is the total I paid INTO an investment and I'm attempting to determine it's current value with the interest added in.

Again, dollars, interest, and dates are for the example only.

 

[FlyerQ]

By your explanation, FV might work since, even thought were at the end of the timeline now, I can always view the result as if I was at the beginning.

If you were promised a series of monthly payment at a particular rate of interest rate, however you received a different set of payments the calculation would be

FV of Promised Payments - Sum of received payments = Wind/(Short) fall

So you don't need to discount the received payments if you were promised a fixed payment schedule. You simple need to know the difference of received payments from the future value of the original promise.

Not sure I'm reading between the lines properly.

 

[FlyerQ]

In my example, $19,750 is the total I paid INTO an investment and I'm attempting to determine it's current value with the interest added in.

Again, dollars, interest, and dates are for the example only.

Yes, you want the future value of your cash flows at whatever rate is agreed upon if, for example, someone is buying you out of a house, boat, airplane, or business.

 

[AggieMike88]

If you were promised a series of monthly payment at a particular rate of interest rate, however you received a different set of payments the calculation would be

FV of Promised Payments - Sum of received payments = Wind/(Short) fall

So you don't need to discount the received payments if you were promised a fixed payment schedule. You simple need to know the difference of received payments from the future value of the original promise.

Not sure I'm reading between the lines properly.

No promised payments. Just a stream of inputs that I now wish to begin cashing out of.

Recurring dollar amount of inputs did change a few times over the total time period, but I know believe I can do that in stages as FV(1), FV(2), FV(3) for the different input values and then add up the sum.

 

[eetrojan]

I'm not willing to reveal the details of the transaction. But flip this to say I invested money into a family entity on a particular schedule and I'm trying to determine what my "cash out" number needs to be.

OK. Got it. For some reason, when I think "present value" I am always trying to determine what something is worth today, versus some date in the future.

I originally used the PV function and note that there's also an FV function for Future Values, and the fields are the same. I didn't do anything but change the "today's date" in my sample sheet and it seems to be calculating the Future Value of the cash flows just fine even though I used the PV function. In other words, either will work fine, but you may need to add a negative sign if you want the numbers to be positive rather than negative, or vice versa:

It passes the giggle check since $19,750 invested in small chunks over a five month would require a cash-out payment of $20,038.59 to get you your 6%, for a net interest gain of $288.59:

 

[FlyerQ]

It's been a long time, but I'll take a stab at it. Your dates suggest a bi-monthly payment stream, but I think you can use the PV function provided that your interest is compounding every day (or providing that you're willing to assume that for the purposes of the calculation). Simply add a third column C and then put the PV function in each cell to figure out the present value of each cash flow, then sum them up, something like this.

The formula from my test sheet is =-PV($B$5/365,A10-$B$4,,B10)

(Note that I manually added the $ prefixes to some of the references in the first cell (e.g. $B$5 rather than B5) so that they wouldn't change when I copy and pasted the formula into the other cells):

This approach provides daily compounding btw.

Again effective rate is trivially different (6.16% pa versus 6.18%)

* just noted he disclosed this

 

[AggieMike88]

Got it all to work and my question has been answered.

Thanks all!

Answer was =-1*FV(rate/365,past_date-todays_date,,$dollars)

 

[denverpilot]

Buying a Garmin, Jay? Heh. Kidding.