diff --git a/kmymoney/plugins/views/reports/core/CMakeLists.txt b/kmymoney/plugins/views/reports/core/CMakeLists.txt --- a/kmymoney/plugins/views/reports/core/CMakeLists.txt +++ b/kmymoney/plugins/views/reports/core/CMakeLists.txt @@ -3,6 +3,7 @@ endif() set (libreports_a_SOURCES + cashflowlist.cpp kreportchartview.cpp reportaccount.cpp listtable.cpp diff --git a/kmymoney/plugins/views/reports/core/cashflowlist.h b/kmymoney/plugins/views/reports/core/cashflowlist.h new file mode 100644 --- /dev/null +++ b/kmymoney/plugins/views/reports/core/cashflowlist.h @@ -0,0 +1,75 @@ +/* + * Copyright 2007 Sascha Pfau + * Copyright 2018 Ralf Habacker + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU General Public License as + * published by the Free Software Foundation; either version 2 of + * the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program. If not, see . + */ + +/* + * This file contains code from the func_xirr and related methods of + * financial.cpp from KOffice by Sascha Pfau. Sascha agreed to relicense + * those methods under GPLv2 or later. + */ + +#ifndef CASHFLOWLIST_H +#define CASHFLOWLIST_H + +#include "mymoneymoney.h" + +#include +#include + +class CashFlowListItem +{ +public: + CashFlowListItem() {} + CashFlowListItem(const QDate& _date, const MyMoneyMoney& _value): m_date(_date), m_value(_value) {} + bool operator<(const CashFlowListItem& _second) const { + return m_date < _second.m_date; + } + bool operator<=(const CashFlowListItem& _second) const { + return m_date <= _second.m_date; + } + bool operator>(const CashFlowListItem& _second) const { + return m_date > _second.m_date; + } + const QDate& date() const { + return m_date; + } + const MyMoneyMoney& value() const { + return m_value; + } + +private: + QDate m_date; + MyMoneyMoney m_value; +}; + +/** + * Cash flow analysis tools for investment reports + */ +class CashFlowList: public QList +{ +public: + CashFlowList() {} + double XIRR(double rate = 0.1) const; + MyMoneyMoney total() const; + void dumpDebug() const; + +private: + double xirrResult(double rate) const; + double xirrResultDerive(double rate) const; +}; + +#endif // CASHFLOWLIST_H diff --git a/kmymoney/plugins/views/reports/core/cashflowlist.cpp b/kmymoney/plugins/views/reports/core/cashflowlist.cpp new file mode 100644 --- /dev/null +++ b/kmymoney/plugins/views/reports/core/cashflowlist.cpp @@ -0,0 +1,166 @@ +/* + * Copyright 2007 Sascha Pfau + * Copyright 2018 Ralf Habacker + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU General Public License as + * published by the Free Software Foundation; either version 2 of + * the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program. If not, see . + */ + +/* + * This file contains code from the func_xirr and related methods of + * financial.cpp from KOffice by Sascha Pfau. Sascha agreed to relicense + * those methods under GPLv2 or later. + */ + +#include "cashflowlist.h" + +#include "mymoneyexception.h" + +#include +#include + +#include + +/** + * Calculates the internal rate of return for a non-periodic + * series of cash flows. The calculation is based on a 365 days + * per year basis, ignoring leap years. + * In case the internal rate of return could not be calculated + * a MyMoneyException is raised. + * + * @param rate optional guess for rate + * @return internal rate of return + */ +double CashFlowList::XIRR(double rate) const +{ + if (size() < 2) + throw MYMONEYEXCEPTION("illegal argument exception"); + + double resultRate = rate; + + // define max epsilon + static const double maxEpsilon = 1e-10; + + // max number of iterations + static const int maxIter = 50; + + // Newton's method - try to find a res, with a accuracy of maxEpsilon + double newRate, rateEpsilon, resultValue; + int iter = 0; + bool contLoop = false; + int iterScan = 0; + bool resultRateScanEnd = false; + + // First the inner while-loop will be executed using the default Value resultRate + // or the user guessed resultRate if those do not deliver a solution for the + // Newton's method then the range from -0.99 to +0.99 will be scanned with a + // step size of 0.01 to find resultRate's value which can deliver a solution + // source hint: + // - outer loop from libreoffice + // - inner loop from KOffice + do { + if (iterScan >=1) + resultRate = -0.99 + (iterScan -1)* 0.01; + + do { + resultValue = xirrResult(resultRate); + newRate = resultRate - resultValue / xirrResultDerive(resultRate); + rateEpsilon = fabs(newRate - resultRate); + resultRate = newRate; + contLoop = (rateEpsilon > maxEpsilon) && (fabs(resultValue) > maxEpsilon); + } while (contLoop && (++iter < maxIter)); + iter = 0; +#ifdef Q_CC_MSVC + if (_isinf(resultRate) || _isnan(resultRate) || + _isinf(resultValue) || _isnan(resultValue)) +#else + if (std::isinf(resultRate) || std::isnan(resultRate) || + std::isinf(resultValue) || std::isnan(resultValue)) +#endif + contLoop = true; + iterScan++; + resultRateScanEnd = (iterScan >= 200); + } while(contLoop && !resultRateScanEnd); + + if (contLoop) + throw MYMONEYEXCEPTION("illegal argument exception"); + return resultRate; +} + +/** + * Calculates the resulting amount for the passed interest rate + * + * @param rate interest rate + * @return resulting amount + */ +double CashFlowList::xirrResult(double rate) const +{ + double r = rate + 1.0; + double result = at(0).value().toDouble(); + const QDate &date0 = at(0).date(); + + for(int i = 1; i < size(); i++) { + double e_i = date0.daysTo(at(i).date()) / 365.0; + result += at(i).value().toDouble() / pow(r, e_i); + } + return result; +} + +/** + * Calculates the first derivation of the resulting amount + * + * @param rate interest rate + * @return first derivation of resulting amount + */ +double CashFlowList::xirrResultDerive(double rate) const +{ + double r = rate + 1.0; + double result = 0; + const QDate &date0 = at(0).date(); + + for(int i = 1; i < size(); i++) { + double e_i = date0.daysTo(at(i).date()) / 365.0; + result -= e_i * at(i).value().toDouble() / pow(r, e_i + 1.0); + } + return result; +} + +/** + * Return the sum of all payments + * + * @return sum of all payments + */ +MyMoneyMoney CashFlowList::total() const +{ + MyMoneyMoney result; + + const_iterator it_cash = begin(); + while (it_cash != end()) { + result += (*it_cash).value(); + ++it_cash; + } + + return result; +} + +/** + * dump all payments + */ +void CashFlowList::dumpDebug() const +{ + const_iterator it_item = begin(); + while (it_item != end()) { + qDebug() << (*it_item).date().toString(Qt::ISODate) << " " << (*it_item).value().toString(); + ++it_item; + } +} diff --git a/kmymoney/plugins/views/reports/core/querytable.h b/kmymoney/plugins/views/reports/core/querytable.h --- a/kmymoney/plugins/views/reports/core/querytable.h +++ b/kmymoney/plugins/views/reports/core/querytable.h @@ -16,15 +16,6 @@ * along with this program. If not, see . */ -/*************************************************************************** - * * - * This program is free software; you can redistribute it and/or modify * - * it under the terms of the GNU General Public License as published by * - * the Free Software Foundation; either version 2 of the License, or * - * (at your option) any later version. * - * * - ***************************************************************************/ - #ifndef QUERYTABLE_H #define QUERYTABLE_H @@ -44,12 +35,12 @@ #include "mymoneymoney.h" class MyMoneyReport; +class CashFlowList; namespace reports { class ReportAccount; -class CashFlowList; /** * Calculates a query of information about the transaction database. @@ -78,94 +69,14 @@ void constructPerformanceRow(const ReportAccount& account, TableRow& result, CashFlowList &all) const; void constructCapitalGainRow(const ReportAccount& account, TableRow& result) const; MyMoneyMoney helperROI(const MyMoneyMoney& buys, const MyMoneyMoney& sells, const MyMoneyMoney& startingBal, const MyMoneyMoney& endingBal, const MyMoneyMoney& cashIncome) const; - MyMoneyMoney helperIRR(const CashFlowList& all) const; + QString helperIRR(const CashFlowList& all) const; void constructSplitsTable(); bool linkEntries() const final override { return true; } private: enum InvestmentValue {Buys = 0, Sells, BuysOfSells, SellsOfBuys, LongTermBuysOfSells, LongTermSellsOfBuys, BuysOfOwned, ReinvestIncome, CashIncome, End}; }; -// -// Cash Flow analysis tools for investment reports -// - -class CashFlowListItem -{ -public: - CashFlowListItem() {} - CashFlowListItem(const QDate& _date, const MyMoneyMoney& _value): m_date(_date), m_value(_value) {} - bool operator<(const CashFlowListItem& _second) const { - return m_date < _second.m_date; - } - bool operator<=(const CashFlowListItem& _second) const { - return m_date <= _second.m_date; - } - bool operator>(const CashFlowListItem& _second) const { - return m_date > _second.m_date; - } - const QDate& date() const { - return m_date; - } - const MyMoneyMoney& value() const { - return m_value; - } - MyMoneyMoney NPV(double _rate) const; - - static void setToday(const QDate& _today) { - m_sToday = _today; - } - const QDate& today() const { - return m_sToday; - } - -private: - QDate m_date; - MyMoneyMoney m_value; - - static QDate m_sToday; -}; - -class CashFlowList: public QList -{ -public: - CashFlowList() {} - MyMoneyMoney NPV(double rate) const; - double IRR() const; - MyMoneyMoney total() const; - void dumpDebug() const; - - /** - * Function: XIRR - * - * Compute the internal rate of return for a non-periodic series of cash flows. - * - * XIRR ( Values; Dates; [ Guess = 0.1 ] ) - **/ - double calculateXIRR() const; - -protected: - CashFlowListItem mostRecent() const; - -private: - /** - * helper: xirrResult - * - * args[0] = values - * args[1] = dates - **/ - double xirrResult(double& rate) const; - - /** - * - * helper: xirrResultDerive - * - * args[0] = values - * args[1] = dates - **/ - double xirrResultDerive(double& rate) const; -}; - } #endif // QUERYREPORT_H diff --git a/kmymoney/plugins/views/reports/core/querytable.cpp b/kmymoney/plugins/views/reports/core/querytable.cpp --- a/kmymoney/plugins/views/reports/core/querytable.cpp +++ b/kmymoney/plugins/views/reports/core/querytable.cpp @@ -16,21 +16,6 @@ * along with this program. If not, see . */ -/*************************************************************************** - * * - * This program is free software; you can redistribute it and/or modify * - * it under the terms of the GNU General Public License as published by * - * the Free Software Foundation; either version 2 of the License, or * - * (at your option) any later version. * - * * - ***************************************************************************/ - -/**************************************************************************** - Contains code from the func_xirr and related methods of financial.cpp - - KOffice 1.6 by Sascha Pfau. Sascha agreed to relicense those methods under - GPLv2 or later. -*****************************************************************************/ - #include "querytable.h" #include @@ -49,6 +34,7 @@ // ---------------------------------------------------------------------------- // Project Includes +#include "cashflowlist.h" #include "mymoneyfile.h" #include "mymoneyaccount.h" #include "mymoneysecurity.h" @@ -67,181 +53,6 @@ namespace reports { -// **************************************************************************** -// -// CashFlowListItem implementation -// -// Cash flow analysis tools for investment reports -// -// **************************************************************************** - -QDate CashFlowListItem::m_sToday = QDate::currentDate(); - -MyMoneyMoney CashFlowListItem::NPV(double _rate) const -{ - double T = static_cast(m_sToday.daysTo(m_date)) / 365.0; - MyMoneyMoney result(m_value.toDouble() / pow(1 + _rate, T), 100); - - //qDebug() << "CashFlowListItem::NPV( " << _rate << " ) == " << result; - - return result; -} - -// **************************************************************************** -// -// CashFlowList implementation -// -// Cash flow analysis tools for investment reports -// -// **************************************************************************** - -CashFlowListItem CashFlowList::mostRecent() const -{ - CashFlowList dupe(*this); - - qSort(dupe); - - //qDebug() << " CashFlowList::mostRecent() == " << dupe.back().date().toString(Qt::ISODate); - - return dupe.back(); -} - -MyMoneyMoney CashFlowList::NPV(double _rate) const -{ - MyMoneyMoney result; - - const_iterator it_cash = constBegin(); - while (it_cash != constEnd()) { - result += (*it_cash).NPV(_rate); - ++it_cash; - } - - //qDebug() << "CashFlowList::NPV( " << _rate << " ) == " << result << "------------------------" << endl; - - return result; -} - -double CashFlowList::calculateXIRR() const -{ - double resultRate = 0.00001; - - double resultZero = 0.00000; - //if ( args.count() > 2 ) - // resultRate = calc->conv()->asFloat ( args[2] ).asFloat(); - -// check pairs and count >= 2 and guess > -1.0 - //if ( args[0].count() != args[1].count() || args[1].count() < 2 || resultRate <= -1.0 ) - // return Value::errorVALUE(); - -// define max epsilon - static const double maxEpsilon = 1e-5; - -// max number of iterations - static const int maxIter = 50; - -// Newton's method - try to find a res, with a accuracy of maxEpsilon - double rateEpsilon, newRate, resultValue; - int i = 0; - bool contLoop; - - do { - resultValue = xirrResult(resultRate); - - double resultDerive = xirrResultDerive(resultRate); - - //check what happens if xirrResultDerive is zero - //Don't know if it is correct to dismiss the result - if (resultDerive != 0) { - newRate = resultRate - resultValue / resultDerive; - } else { - - newRate = resultRate - resultValue; - } - - rateEpsilon = fabs(newRate - resultRate); - - resultRate = newRate; - contLoop = (rateEpsilon > maxEpsilon) && (fabs(resultValue) > maxEpsilon); - } while (contLoop && (++i < maxIter)); - - if (contLoop) - return resultZero; - - return resultRate; -} - -double CashFlowList::xirrResult(double& rate) const -{ - double r = rate + 1.0; - double res = 0.00000;//back().value().toDouble(); - - QList::const_iterator list_it = constBegin(); - while (list_it != constEnd()) { - double e_i = ((* list_it).today().daysTo((* list_it).date())) / 365.0; - MyMoneyMoney val = (* list_it).value(); - - if (e_i < 0) { - res += val.toDouble() * pow(r, -e_i); - } else { - res += val.toDouble() / pow(r, e_i); - } - ++list_it; - } - - return res; -} - - -double CashFlowList::xirrResultDerive(double& rate) const -{ - double r = rate + 1.0; - double res = 0.00000; - - QList::const_iterator list_it = constBegin(); - while (list_it != constEnd()) { - double e_i = ((* list_it).today().daysTo((* list_it).date())) / 365.0; - MyMoneyMoney val = (* list_it).value(); - - res -= e_i * val.toDouble() / pow(r, e_i + 1.0); - ++list_it; - } - - return res; -} - -double CashFlowList::IRR() const -{ - double result = 0.0; - - // set 'today', which is the most recent of all dates in the list - CashFlowListItem::setToday(mostRecent().date()); - - result = calculateXIRR(); - return result; -} - -MyMoneyMoney CashFlowList::total() const -{ - MyMoneyMoney result; - - const_iterator it_cash = constBegin(); - while (it_cash != constEnd()) { - result += (*it_cash).value(); - ++it_cash; - } - - return result; -} - -void CashFlowList::dumpDebug() const -{ - const_iterator it_item = constBegin(); - while (it_item != constEnd()) { - qDebug() << (*it_item).date().toString(Qt::ISODate) << " " << (*it_item).value().toString(); - ++it_item; - } -} - // **************************************************************************** // // QueryTable implementation @@ -1254,20 +1065,15 @@ return returnInvestment; } -MyMoneyMoney QueryTable::helperIRR(const CashFlowList &all) const +QString QueryTable::helperIRR(const CashFlowList &all) const { - MyMoneyMoney annualReturn; try { - double irr = all.IRR(); -#ifdef Q_CC_MSVC - annualReturn = MyMoneyMoney(_isnan(irr) ? 0 : irr, 10000); -#else - annualReturn = MyMoneyMoney(std::isnan(irr) ? 0 : irr, 10000); -#endif - } catch (QString e) { - qDebug() << e; + return MyMoneyMoney(all.XIRR(), 10000).toString(); + } catch (MyMoneyException &e) { + qDebug() << e.what(); + all.dumpDebug(); + return QString(); } - return annualReturn; } void QueryTable::sumInvestmentValues(const ReportAccount& account, QList& cfList, QList& shList) const @@ -1599,10 +1405,9 @@ } MyMoneyMoney returnInvestment = helperROI(buysTotal - reinvestIncomeTotal, sellsTotal, startingBal, endingBal, cashIncomeTotal); - MyMoneyMoney annualReturn = helperIRR(all); result[ctBuys] = buysTotal.toString(); - result[ctReturn] = annualReturn.toString(); + result[ctReturn] = helperIRR(all); result[ctReturnInvestment] = returnInvestment.toString(); result[ctEquityType] = MyMoneySecurity::securityTypeToString(file->security(account.currencyId()).securityType()); } @@ -1793,7 +1598,7 @@ // convert map of top accounts with cashflows to TableRow for (QMap::iterator topAccount = (*currencyAccGrp).begin(); topAccount != (*currencyAccGrp).end(); ++topAccount) { qtotalsrow[ctTopAccount] = topAccount.key(); - qtotalsrow[ctReturn] = helperIRR(topAccount.value()).toString(); + qtotalsrow[ctReturn] = helperIRR(topAccount.value()); qtotalsrow[ctCurrency] = currencyAccGrp.key(); currencyGrandCashFlow[currencyAccGrp.key()] += topAccount.value(); // cumulative sum of cashflows of each topaccount m_rows.append(qtotalsrow); // rows aren't sorted yet, so no problem with adding them randomly at the end @@ -1803,7 +1608,7 @@ QMap::iterator currencyGrp = currencyGrandCashFlow.begin(); qtotalsrow[ctTopAccount].clear(); // empty topaccount because it's grand cashflow while (currencyGrp != currencyGrandCashFlow.end()) { - qtotalsrow[ctReturn] = helperIRR(currencyGrp.value()).toString(); + qtotalsrow[ctReturn] = helperIRR(currencyGrp.value()); qtotalsrow[ctCurrency] = currencyGrp.key(); m_rows.append(qtotalsrow); ++currencyGrp; diff --git a/kmymoney/plugins/views/reports/core/tests/querytable-test.cpp b/kmymoney/plugins/views/reports/core/tests/querytable-test.cpp --- a/kmymoney/plugins/views/reports/core/tests/querytable-test.cpp +++ b/kmymoney/plugins/views/reports/core/tests/querytable-test.cpp @@ -24,6 +24,7 @@ #include #include "tests/testutilities.h" +#include "cashflowlist.h" #include "querytable.h" #include "mymoneyinstitution.h" #include "mymoneyaccount.h" @@ -327,7 +328,7 @@ void QueryTableTest::testCashFlowAnalysis() { // - // Test IRR calculations + // Test XIRR calculations // CashFlowList list; @@ -343,16 +344,56 @@ list += CashFlowListItem(QDate(2004, 9, 21), MyMoneyMoney(5.0)); list += CashFlowListItem(QDate(2004, 10, 16), MyMoneyMoney(-2037.0)); - MyMoneyMoney IRR(list.IRR(), 1000); - - QVERIFY(IRR == MyMoneyMoney(1676, 1000)); + MyMoneyMoney XIRR(list.XIRR(), 1000); + QVERIFY(XIRR == MyMoneyMoney(1676, 1000)); list.pop_back(); list += CashFlowListItem(QDate(2004, 10, 16), MyMoneyMoney(-1358.0)); - IRR = MyMoneyMoney(list.IRR(), 1000); + XIRR = MyMoneyMoney(list.XIRR(), 1000); + QVERIFY(XIRR.isZero()); + + // two entries + list.clear(); + list += CashFlowListItem(QDate(2005, 9, 22), MyMoneyMoney(-3472.57)); + list += CashFlowListItem(QDate(2009, 3, 18), MyMoneyMoney(6051.36)); + + XIRR = MyMoneyMoney(list.XIRR(), 1000); + QCOMPARE(XIRR.toDouble(), MyMoneyMoney(173, 1000).toDouble()); + + // check ignoring zero values + list += CashFlowListItem(QDate(1998, 11, 1), MyMoneyMoney(0.0)); + list += CashFlowListItem(QDate(2017, 8, 11), MyMoneyMoney(0.0)); + + XIRR = MyMoneyMoney(list.XIRR(), 1000); + QCOMPARE(XIRR.toDouble(), MyMoneyMoney(173, 1000).toDouble()); + + list.pop_back(); + // former implementation crashed + list += CashFlowListItem(QDate(2014, 8, 11), MyMoneyMoney(0.0)); + + XIRR = MyMoneyMoney(list.XIRR(), 1000); + QCOMPARE(XIRR.toDouble(), MyMoneyMoney(173, 1000).toDouble()); - QVERIFY(IRR.isZero()); + // different ordering + list.clear(); + list += CashFlowListItem(QDate(2004, 3, 18), MyMoneyMoney(6051.36)); + list += CashFlowListItem(QDate(2005, 9, 22), MyMoneyMoney(-3472.57)); + + XIRR = MyMoneyMoney(list.XIRR(), 1000); + QCOMPARE(XIRR.toDouble(), MyMoneyMoney(-307, 1000).toDouble()); + + + // not enough entries + list.pop_back(); + + bool result = false; + try { + XIRR = MyMoneyMoney(list.XIRR(), 1000); + } catch (MyMoneyException &e) { + result = true; + } + QVERIFY(result); } void QueryTableTest::testAccountQuery()