very very long formulas excel 2002

[anon125]

=+AMOUNT*rate%/365*(DATE(YEAR(YEAREND),MONTH(MATURE),DAY(MATURE))-DATE(YEAR(YEAREND),MONTH(1),DAY(1)))+((((AMOUNT+(AMOUNT*rate%))*rate%)/365)*(YEAREND-(DATE(YEAR(YEAREND),MONTH(MATURE),DAY(MATURE)))))

is just a part of the formulas i have to use!
yes i am definitely not an expert!

anyway... is there some way of avoiding this?
like just have a formula that says if this then go to say C1 where the needed formula is.

the above works out the interest up to the compounding date and the interest hafter that date. for one year.

thanks all

 

[StephenCrump]

This is a very dodgy looking formula! I tested it quickly and produced some silly results. I'd be very surprised if this formula does what it purports to do.

When you say this is part of the formulas I have to use, does this mean that you are stuck with having to run someone else's model? If so, you have my sympathy. Been there, done that!

In general, all I can suggest is that you look to replace all the convoluted formula with simpler formulae, perhaps taking two or three intermediate steps so that each step is easily understood, and the calculation easily verified.

As a start, can you perhaps provide an actual example of the interest calculation you are doing here, e.g. what are the values of Amount? Rate? YearEnd? Mature? What do you think the interest calculation should be, and what is the answer you think is correct?

The formulae will definitely be simpler than what you've got. But unfortunately it's hard to tell from the existing formula what the calculation is meant to be.

 

[anon125]

well i am doing what you said. formulas are made bit by bit on different lines at the moment.
formula has to cover all sorts of situations.
compounding mid year is just one of them!
thanks