Issue 74069 - ODFF: YEARFRAC with basis=1 actual/actual calculation slightly incorrect
Summary: ODFF: YEARFRAC with basis=1 actual/actual calculation slightly incorrect
Status: ACCEPTED
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: recent-trunk
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2007-01-31 19:12 UTC by ooo
Modified: 2013-08-07 15:13 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description ooo 2007-01-31 19:12:48 UTC
The function YEARFRAC needs some tweaking. Excel claims to implement the AFB
actual/actual method for basis=1. We seem to do something slightly different,
looks like a 1/366 denominator if calculating the difference between 2000-12-31
and 2001-01-01 although a 29th of February is not contained in that time frame.
For documentation on the several methods see
http://www.isda.org/c_and_a/pdf/mktc1198.pdf
Comment 1 daniel.rentz 2007-12-18 12:39:48 UTC
target
Comment 3 dipesh 2010-04-16 19:22:49 UTC
I can confirm the bug in oocalc and we just fixed it in KSpread.

What we do is something like;

    switch (basis) {
    case 1: {
        nYears = date2.year() - date1.year() + 1;
        for (int y = date1.year(); y <= date2.year(); ++y)
            peryear += QDate::isLeapYear(y) ? 366 : 365;
        if (QDate(date1.year() + 1, date1.month(), date1.day()) >= date2) {
            nYears = 1;
            peryear = 365;
            if (QDate::isLeapYear(date1.year()) && date1.month() <= 2) peryear
= 366;
            else if (QDate::isLeapYear(date2.year()) && date2.month() > 2)
peryear = 366;
            else if (date2.month() == 2 && date2.day() == 29) peryear = 366;
        }
        peryear = peryear / (long double) nYears;
        nYears = 0;
        break;
    } break;
    case 2: {
        peryear = 360;
    } break;
    case 3: {
        peryear = 365;
    } break;
    case 4: {
        days = days360(date1, date2, 1);
        peryear = 360;
    } break;
    default: {
        days = days360(date1, date2, 0);
        peryear = 360;
    }
    }

int days360(int day1, int month1, int year1, bool leapYear1, int day2, int
month2, int year2, bool leapYear2, bool usaMethod)
{
    if (usaMethod) { // US method
        if (day1 == 31) {
            day1 = 30;
            if (day2 == 31)
                day2 = 30;
        }
        else if (day1 == 30 && day2 == 31)
            day2 = 30;
        else if (month1 == 2 && (day1 == 29 || (day1 == 28 && ! leapYear1))) {
            day1 = 30;
            if (month2 == 2 && (day2 == 29 || (day2 == 28 && ! leapYear2)))
                day2 = 30;
        }
    } else { // European method
        if (day1 == 31) {
            day1 = 30;
        }
        if (day2 == 31) {
            day2 = 30;
        }
    }
    return day2 + month2 * 30 + year2 * 360 - day1 - month1 * 30 - year1 * 360;
}