Hello this is my first post and hope I am in the right sub-forum. I apologize in advance for a long post, but hope someone
can help me.
I am generally pretty good with excel but something has me stumped and I feel there is an easier way
than what I am doing.
Essentially, there are three columns of data that I am interested in at the moment within
each row (Lets call it A, B, and C). Each of the columns will be a dollar amount. A will be entered before B and B before C
as they are to track subsequent payments. The interesting thing is that the value in the columns will dictate what the
interest will be and thus the "Total Return" as I am calling it. As such, I have elected to use IF statements.
A little more background before I dive into actual excel speak; the sum amounts in A, B, and C will generate interest
at different levels (Lets call the sum of A,B, and C = D). The first $1,000 will generate interest that compounds daily of an
annualized rate of 3%. Any amount above $1,000 will generate interest that compounds daily of an annualized rate of 7%.
[These numbers are for example purpose and hopefully clear enough to be illustrative on how to formate a formula, my actual
numbers are needlessly complicated for this exercise... sig figs always inviting themselves to the party]. Anyway, I am
looking for a way to input a formula so that excel recognizes that once previous payments reach $1,000 to start using the new
interest rate (7%) and apply it to any subsequent payments if applicable.
I was able to use this IF statement
for column A:
=IF(ColumnA>1000,(1000*((1+0.03/365)^(365*(Months to Payment/12))))+((Column
A-1000)*((1+0.07/365)^(365*(Months to Payment/12)))),(Column A*((1+0.03/365)^(365*(Months to Payment/12)))))
the bold text indicates arbitrary positions on the excel sheet. I know there has to be an easier way, however, the interest
calculator built into excel does not seem to suit my needs as an end date is required. As you can see from above, the end
date is unknown to me at the moment as no maturity date is established. Instead, I built a formula to recognize the start and
end dates of the transaction to establish "Months to Payment".
Besides being overly complex, the above
formula does not address B or C at all. I tried tailoring IF(AND( and IF(OR( statements to consider A, B, and C at the same
time but my first 3 or so tries didn't work as I wanted them to. Not to mention, I have no idea how I could make the sheet
realize that for a hypothetical $500 payment in B, perhaps $136.90 needs to accrue 3% while the remaining $363.10 should
accrue 7% (depending on where A puts me at the $1,000 transition point). It would be easy if I knew A would always be either
above or below $1,000, yet I have no idea what it will be and could be anything from a few dollars to a couple thousand
I will try to prepare a sample sheet now and upload it shortly as it is most likely easier to understand
by actually seeing it. If everything above could be settled, I would be very happy. I will add 1 more detail below that
complicates the situation. However, I value a formula to address the issue listed to this point much more than the
complication I will list below.
A, B, and C are all quantitative payments. Imagine X, Y, and Z qualitatively describe the type of payment A, B, and C are
(where X describes A, etc down the row). There are three options for the types of payments X, Y, and Z can be: 1, 2, and 3. 1
and 2 have no effect on anything and are strictly for more precise record keeping. However, option 3 demands that the
interest automatically be calculated at 7%. For example, imagine if 1 = Food, 2 = Gas, 3 = Entertainment; any column (A, B,
C) described by 3 would accrue 7%. Again, this complication is secondary to my first concern but would be awesome if I could
automate the whole thing.
I will put together a sample sheet now. Thank you in advance for all your help.