MongoDB Aggregation Pipeline: Approximation with Newton’s Method
In this article I will show you an implementation of Newton’s method in an Aggregation Pipeline.
Newton’s Method
MongoDB Aggregation Pipeline: Approximation with Newton’s Method
In this article I will show you an implementation of Newton’s method in an Aggregation Pipeline.
$function is deprecated and I was looking to implement it natively in an Aggregation Pipeline without using any external library.
As example let’s take the XIRR function which calculates the Internal Rate of Return (IRR) for a series of cash flows. For a series of cash flows (N) it is given as:
where:
- di = the ith payment date.
- d1 = the 1st payment date.
- Pi = the ith payment.
db.collection.insertMany([
{ _id: 1, payment: -10000, date: ISODate('2022-01-01') },
{ _id: 2, payment: 2750, date: ISODate('2022-03-01') },
{ _id: 3, payment: 4250, date: ISODate('2022-10-30') },
{ _id: 4, payment: 3250, date: ISODate('2023-02-15') },
{ _id: 5, payment: 2750, date: ISODate('2023-04-01') }
])With Internal Rate of Return the calculation looks like this one:
const rate = 0.3748585976776016
db.collection.aggregate([
{
$group: {
_id: null,
data: { $push: "$$ROOT" },
d1: { $min: "$date" },
}
},
{
$project: {
xirr: {
$sum: {
$map: {
input: "$data",
as: 'data',
in: {
$divide: ["$$data.payment",
{
$pow: [
{ $add: [1, rate] },
{ $divide: [{ $dateDiff: { startDate: "$d1", endDate: "$$data.date", unit: 'day' } }, 365] }]
}
]
}
}
}
}
}
}
])
Desired output -> xirr ≈0 It is not possible to solve above equation to rate, thus it must be iterated. With native Aggregation Pipeline it is not possible to run recursive functions or condition-controlled loops. Thus, we assume a max. number of 100 iterations.
Numerical calculation with difference quotient
For newton’s method you need to evaluate the deviation of your function. In real life, this can be very difficult and a numerical evaluation is more suitable.
We calculate the first value and then based on 2-Point Equation of Line we calculate next iterations.
Formula for next rate iteration
function xirr(arg) {
// Use this function to improve readability of the code
return {
$sum: {
$map: {
input: "$data",
as: 'data',
in: {
$divide: ["$$data.payment",
{
$pow: [
{ $add: arg },
{ $divide: [{ $dateDiff: { startDate: "$d1", endDate: "$$data.date", unit: 'day' } }, 365] }]
}
]
}
}
}
}
}
db.collection.aggregate([
{
$group: {
_id: null,
data: { $push: "$$ROOT" },
d1: { $min: "$date" },
}
},
{
$set: {
xirr: {
$let: {
vars: { guess: 0.1, y2_y1: 0.0000001 },
in: {
$reduce: {
input: { $range: [2, 101] },
initialValue: [{
// calculate inital value
i: 1,
rate: "$$guess",
sum: xirr([1, "$$guess"]),
x2_x1: {
$subtract: [
xirr([1, "$$guess", "$$y2_y1"]),
xirr([1, "$$guess"])
]
}
}],
in: {
$concatArrays: [
"$$value",
[{
$cond: {
if: {
// condition to stop iterations
$lt: [{ $abs: { $last: "$$value.sum" } }, 0.000001]
},
then: null,
else: {
$let: {
vars: {
rate: {
// calculate rate using Equation of a Line from 2 Points
$subtract: [
{ $last: "$$value.rate" },
{
$multiply: [
{ $last: "$$value.sum" },
{ $divide: ["$$y2_y1", { $last: "$$value.x2_x1" }] }
]
}
]
}
},
in: {
i: "$$this",
rate: "$$rate",
sum: xirr([1, "$$rate"]),
x2_x1: {
$subtract: [
xirr([1, "$$rate", "$$y2_y1"]),
xirr([1, "$$rate"])
]
}
}
}
}
}
}]
]
}
}
}
}
}
}
},
{ $set: { xirr: { $filter: { input: "$xirr", cond: { $ne: ["$$this", null] } } } } },
{
$project: {
result: { $last: "$xirr.rate" },
data: 1,
xirr: 1
}
}
])Result will be this one:
[{
result: 0.3748585976776016,
data: [
{ _id: 1, payment: -10000, date: ISODate('2022-01-01') },
{ _id: 2, payment: 2750, date: ISODate('2022-03-01') },
{ _id: 3, payment: 4250, date: ISODate('2022-10-30') },
{ _id: 4, payment: 3250, date: ISODate('2023-02-15') },
{ _id: 5, payment: 2750, date: ISODate('2023-04-01') }
],
xirr: [
{ i: 1, rate: 0.1, sum: 1997.642501419953, x2_x1: -0.0000910145276975527 },
{ i: 2, rate: 0.3194861141353446, sum: 335.0546848435074, x2_x1: -0.00006306108866738214 },
{ i: 3, rate: 0.3726178776243584, sum: 13.031307732403775, x2_x1: -0.00005825209996146441 },
{ i: 4, rate: 0.3748549313513526, sum: 0.021287401081508506, x2_x1: -0.00005806193894386524 },
{ i: 5, rate: 0.3748585976776016, sum: 0.00000005672359293384943, x2_x1: -0.000058061627441929886 }
]
}]As you see after 5 iterations we get a sufficient result of 0.3748585976776016 or 37.5%
Calculation with deviation
When you are smart, then you can deviate your function. In our example it would be this one:
Deviation of XIRR Formula
Expression as aggregation pipeline would be:
function deviate(arg) {
return {
$sum: {
$map: {
input: "$data",
as: 'data',
in: {
$divide: [
{
$multiply: [
-1,
"$$data.payment",
{ $divide: [{ $dateDiff: { startDate: "$d1", endDate: "$$data.date", unit: 'day' } }, 365] }]
},
{
$pow: [
{ $add: [1, arg] },
{
$add: [
{ $divide: [{ $dateDiff: { startDate: "$d1", endDate: "$$data.date", unit: 'day' } }, 365] },
1
]
}
]
}
]
}
}
}
}
}With deviation the next rate values is calculated by
This will end in aggregation pipeline which is a little shorter than above:
db.collection.aggregate([
{
$group: {
_id: null,
data: { $push: "$$ROOT" },
d1: { $min: "$date" },
}
},
{
$set: {
xirr: {
$let: {
vars: { guess: 0.1 },
in: {
$reduce: {
input: { $range: [2, 101] },
initialValue: [{
// calculate inital value
i: 1,
sum: xirr([1, "$$guess"]),
rate: "$$guess"
}],
in: {
$concatArrays: [
"$$value",
[{
$cond: {
if: {
// condition to stop iterations
$lt: [{ $abs: { $last: "$$value.sum" } }, 0.00000001]
},
then: null,
else: {
$let: {
vars: {
rate: {
$subtract: [
{ $last: "$$value.rate" },
{
$divide: [
xirr([1, { $last: "$$value.rate" }]),
deviate({ $last: "$$value.rate" })]
}
]
}
},
in: {
i: "$$this",
rate: "$$rate",
sum: xirr([1, "$$rate"]),
}
}
}
}
}]
]
}
}
}
}
}
}
},
{ $set: { xirr: { $filter: { input: "$xirr", cond: { $ne: ["$$this", null] } } } } },
{
$project: {
result: { $last: "$xirr.rate" },
data: 1,
xirr: 1
}
}
])As expected the output is almost the same as above:
[{
result: 0.3748585976775555,
data: [
{ _id: 1, payment: -10000, date: ISODate('2022-01-01') },
{ _id: 2, payment: 2750, date: ISODate('2022-03-01') },
{ _id: 3, payment: 4250, date: ISODate('2022-10-30') },
{ _id: 4, payment: 3250, date: ISODate('2023-02-15') },
{ _id: 5, payment: 2750, date: ISODate('2023-04-01') }
],
xirr: [
{ i: 1, rate: 0.1, sum: 1997.642501419953 },
{ i: 2, rate: 0.31948610922427223, sum: 335.0547158132647 },
{ i: 3, rate: 0.3726178766491623, sum: 13.031313413127009 },
{ i: 4, rate: 0.3748549313129668, sum: 0.021287623957505275},
{ i: 5, rate: 0.3748585976775555, sum: 0.00000005699121174984611 }
]
}]Considerations and fine-tuning
The numerical approach with difference quotient can be easily adapted to any function used in your application. You just need to replace the function(args) {} part. For the other approach you need some more math.
I assumed max. 100 iterations. In many cases, this will be way to high. In most cases Newton’s Method returns sufficient result after 5–10 iterations. You may lower this limit. On the other hand, you should check if the size of the array is lower than 100, otherwise a solution was not found.
You may adapt the condition to stop the iteration and the step-size according to your requirements.
If you like to get some math background, have a look at
메타데이터
- post_id
- 21df2956e2fa
- slug
- mongodb-aggregation-pipeline-approximation-with-newtons-method-21df2956e2fa
- url
- https://medium.com/@github_80805/mongodb-aggregation-pipeline-approximation-with-newtons-method-21df2956e2fa
- canonical_url
- https://medium.com/@github_80805/mongodb-aggregation-pipeline-approximation-with-newtons-method-21df2956e2fa
- author_url
- https://medium.com/@github_80805
- status
- ok
- fetched_at
- 2026-08-06 08:08:52