very very long formulas excel 2002

anon125

Active Member
Joined
Feb 14, 2008
Messages
380
=+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

 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,192
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 14, 2008
Messages
380
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
 

Forum statistics

Threads
1,137,060
Messages
5,679,387
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top