Assets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frances C - 29 Sep 2007 21:46 GMT

I need a formula that can be use for different kind of asset calculations for
example

a 401k that has a value of $10,000.00 and is multiply by interest rate of
2% and I need to subtract $1000.00 for cost to sell (sometimes that cost to
sell is also presented as 10% instead of $1000.00)

I also have a real estate with a value of $200,000.00 to be mutiply by
interest rate of 0% and to subtract a cost to sell of $150,000.00

Another one is a Saving accont a value of $500.00 and is mutiply by interest
rate of 3% with $0.00 cost to sell

I have try some formulas, I really need one that can work with all the
problems.

a1=value b1=interest rate c1=cost to sell d1=where the formula goes
(answer)

Thanks

Reply to this Message

joeu2004 - 30 Sep 2007 00:18 GMT

On Sep 29, 1:46 pm, Frances C <Franc...@discussions.microsoft.com>
wrote:

> I need a formula that can be use for different kind of asset
> calculations

I feel that I must not understand the problem you are trying to solve
because the financial arithmetic you want to perform seems too
rudimentary. So forgive me if my solution misses the mark entirely.

> for example
>
> a 401k that has a value of $10,000.00 and is multiply by interest rate of
> 2% and I need to subtract $1000.00 for cost to sell (sometimes that cost
> to sell is also presented as 10% instead of $1000.00)

The parenthetical requirement is something of a challenge.
Ostensibly, there is no discernible difference between $1000 and 10%.
You can format 10% and $0.10; and you can format $1000 as 100000%.
(Did I add enough zeros?)

Perhaps it will be sufficient to assume that percentages are always
less than 100% and dollar values are always more than $1. If you
cannot live with that assumption, then it will be necessary to add a
cell to indicate what kind of number you have in the "cost" cell (C1).

> I also have a real estate with a value of $200,000.00 to be mutiply by
> interest rate of 0% and to subtract a cost to sell of $150,000.00
[quoted text clipped - 4 lines]
> a1=value b1=interest rate c1=cost to sell
> d1=where the formula goes (answer)

The formula in D1 could be:

=round(A1*(1+B1) - if(C1<1, A1*C1, C1), 2)

I have made some assumptions about your requirements.

You say that you want the value to be "multiplied by the interest
rate". Ostensibly, that would be A1*B1. But then you say that you
want to subtract the cost. In the first example, that would result in
a negative value because 2%*10000 - 1000 is 200 - 1000. So I ass-u-me
you mean that you want the value __increased__ by the interest rate;
hence A1*(1+B1).

Also, you say that sometimes the cost is "a percentage". A percentage
of what? Your example is deceptive: you write "10% instead of
$1000", where the value is $10,000. That might suggest that you mean
"a percentage of the value before adding interest". That is what I
ass-u-me for the formula above.

But I would expect you mean "a percentage of the increased value,
after adding interest". In that case, the formula might be:

=round(A1*(1+B1) - if(C1<1, A1*(1+B1)*C1, C1), 2)

Alternatively:

=round(if(C1<1, A1*(1+B1)*(1-C1), A1*(1+B1)-C1), 2)

HTH.

Reply to this Message

joeu2004 - 30 Sep 2007 00:32 GMT

PS....

> In the first example, that would result in a negative value
> because 2%*10000 - 1000 is 200 - 1000. So I ass-u-me
> you mean that you want the value __increased__ by the
> interest rate; hence A1*(1+B1).

Well, there is nothing wrong with a negative result. You might sell
something at a loss.

The real reason why I ass-u-me that A1*(1+B1) is what you want is
because I ass-u-me that when you say "interest rate", you really mean
the cumulative growth rate; for example, a 401(k) account that has
increased by 2% over the life of the investment.

Reply to this Message

daddylonglegs - 30 Sep 2007 00:23 GMT

Hello Frances,

it would be more helpful if you gave the results you want. I'm assuming you
want to add the percentage value to the original value so that your first
example would be 10000 with 2% added = 10200 minus 1000 gives a result of
9200.

For that you'd need a formula of

=A1*(1+B1)-C1

but to cater for the possibility that C1 could either contain an amount to
subtract or a percentage to subtract try

=A1*(1+B1)-C1*IF(LEFT(CELL("format",C1))="P",A1*(1+B1),1)

> I need a formula that can be use for different kind of asset calculations for
> example
[quoted text clipped - 16 lines]
>
> Thanks

Reply to this Message

Frances C - 30 Sep 2007 02:50 GMT

I think I did not explain my self very well, When

A1=VALUE
B1=INTEREST RATE
C1=COST TO SELL
D1=(result) it should be ASSET INCOME the result of the value*interest rate
- cost to sell = asset income. This number can not be negative

> I need a formula that can be use for different kind of asset calculations for
> example
[quoted text clipped - 16 lines]
>
> Thanks

Reply to this Message

joeu2004 - 30 Sep 2007 07:56 GMT

On Sep 29, 6:50 pm, Frances C <Franc...@discussions.microsoft.com>
wrote:

> I think I did not explain my self very well, When
> A1=VALUE
> B1=INTEREST RATE
> C1=COST TO SELL
> D1=(result) it should be ASSET INCOME the result of the value*interest rate
> - cost to sell = asset income. This number can not be negative

I think your first example demonstrates that it __can__ be negative,
as I explained previously. However, if you mean that you want the
formula to disallow negative results, perhaps the following is closer
to what you want:

=round(max(0, A1*B1 - if(C1<1, A1*C1, C1)), 2)

Again, that presumes it is acceptable to assume that C1<1 is a
percentage, otherwise it is a dollar amount. Alternatively, you can
use DaddyLongLegs's trick of looking at the cell format.

Reply to this Message

Frances C - 30 Sep 2007 16:04 GMT

for example, I need a one formula that can calculate both of this dilemas

a1=200.00 a1=200.00
b1=6.5% b1=2%
c1=150.00 c1=0.00
d1=63.00 d1=4.00

Thanks joeu2004 for all your help

> On Sep 29, 6:50 pm, Frances C <Franc...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 15 lines]
> percentage, otherwise it is a dollar amount. Alternatively, you can
> use DaddyLongLegs's trick of looking at the cell format.

Reply to this Message

joeu2004 - 30 Sep 2007 18:25 GMT

On Sep 30, 8:04 am, Frances C <Franc...@discussions.microsoft.com>
wrote:

> for example, I need a one formula that can calculate both of this dilemas
> a1=200.00 a1=200.00
> b1=6.5% b1=2%
> c1=150.00 c1=0.00
> d1=63.00 d1=4.00

Please revisit the original formulas that both DaddyLongLegs and I
provided. The result on the left is arrived at by:

200*(1+6.5%) - 150 = 200 + 200*6.5% - 150 = 213 - 150 = 63

In Excel terms:

=A1*(1+B1) - C1

I would round the result because we are talking money. And the
formula becomes necessarily more complicated because you want to allow
for C1 to be a percentage. Hence the final formula (making
assumptions about C1 that I explained earlier):

=round(A1*(1+B1) - if(C1<1, A1*C1, C1), 2)

Previously, you wrote:

> A1=VALUE
> B1=INTEREST RATE
> C1=COST TO SELL
> D1=(result) it should be ASSET INCOME the result of the
> value*interest rate - cost to sell = asset income.
> This number can not be negative

That woud not result in 63 on the left. Do the math:

200*6.5% - 150 = 13 - 150 = -137

Reply to this Message

joeu2004 - 30 Sep 2007 19:07 GMT

Errata....

I wrote:

> 200*(1+6.5%) - 150 = 200 + 200*6.5% - 150 = 213 - 150 = 63

I see the problem now: that arithmetic does not work for the example
of the left, which was apparently calculated as follows:

200*2% - 0 = 4 - 0 = 4

Without more information, it is impossible to provide "the right"
solution for you. You provided examples of the first two cases in
your original posting. It might help if you provide a numerical
example (similar to your latest posting) for the third case.

First, I am suspicious of why the cases are calculated differently.
It might help to know what problem you are really trying to solve --
for example, US capital gains.

Second, assuming your problem specification is correct, I think the
best way to solve it would be to have another column in which you
specify the case you are dealing with. Then the formula in D1 can be
an IF() formula that uses different calculations depending on the case
type.

And I wrote:

> formula becomes necessarily more complicated because you want
> to allow for C1 to be a percentage. Hence the final formula (making
> assumptions about C1 that I explained earlier):
>
> =round(A1*(1+B1) - if(C1<1, A1*C1, C1), 2)

Sorry for the hasty reiteration. I suspect that "A1*C1" should be
replaced by A1*(1+B1)*C1.

Reply to this Message

Frances C - 30 Sep 2007 23:50 GMT

Thanks for all your help. Since what I want to lcalculate is annual income of
assets. every asset involves a different formula. Thanks for your time. I
need to think a about a different way of calculation of such assets. Thanks
for your help and for the formulas you provided. Will talk soon again.

Frances

> Errata....
>
[quoted text clipped - 30 lines]
> Sorry for the hasty reiteration. I suspect that "A1*C1" should be
> replaced by A1*(1+B1)*C1.

Reply to this Message