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)))))
Where 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 dollars.
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.
Complication:
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.
Bookmarks