No way to know for sure.
The closest you are likely to get, in terms of first hand accounts, will be found in Thomas Taylor’s
Biography of, “The Life of William Cowper, Esq (1833).”
I found the quote. The volume was neither of the ones I suggested in my comments.
Several years ago, however, I had read everything I could find on William Cowper. His
particular ‘thorn in the flesh’ was an intimate concern of mine at the time. This is what
first drew me to Olney, in fact.
Now that I remember where I read this, I realize that there is no point in looking any further for a closer account than 33 years of Cowper’s passing. Thomas Taylor was privy to documents such as journals and letters (as is apparent by the references he makes to testimony of individuals) which I was never able to find extant records for. Not to mention, first hand accounts of John Newton’s adopted daughter, Cowper’s nephew (John Johnson), and near acquaintances who had direct association with Cowper (sorry about the names, I think Newton’s adopted daughter was the daughter of the rev. Unwin, and his widow. [better check that though]).
Regardless, there are some significant clarifications in the “The Letters of John Newton.” One of these being a clarification of the frequently referenced account of Cowper’s Nephew (John Johnson). Cowper’s nephew was present at his passing. The following is taken from Rev. Newton's letter to Mrs. Hannah More, dated May 24, 1800.
"My most dear and intimate friend, William Cowper, has obtained a release from all his distresses. I preached a funeral sermon for him on the 11th inst. from Eccles. ii. 2, 3. Why was he, who both by talents and disposition seemed qualified, if it were possible, to reform the age in that the bush which Moses saw all in flames was a fit emblem of his case?
The Lord's thoughts and ways are so much above ours, that it becomes us rather to lie in the dust in adoration and silence than to inquire presumptuously into the grounds of his proceedings; yet I think we may draw some lessons from his sufferings. I wish to learn from them thankfulness for the health and peace with which I have been favoured, and caution not to depend upon whatever gifts, abilities or usefulness, past comforts or experiences, have been afforded me. In all these respects, my friend was, during a part of his life, greatly my superior. He lived(though not without short conflicts), in point of comfort and conduct, far above the common standard, for about ten years; and for twenty-seven years afterwards he knew not one peaceful day (The Letters of John Newton pp 355-56.")
The following is a third hand verbal account finally recorded in 1906 by Stopford Brooke, that Cowper’s nephew made note of the expression on Cowper’s face the half-hour before he had passed:
"Dr. Moule, the Bishop of Durham, vouches for the following incident:
'When Cowper lay dying there did not come to him one gleam of hope. John Johnson, his nephew, was watching by him, and in thought was strongly tempted towards a blank infidelity by the sight of such goodness seemingly so deserted. Then, on a sudden, there came a change. The dying face was irradiated as with surprise of joy "unspeakable and full of glory." Cowper lay speechless, motionless, but visibly enraptured for the last half-hour before the ceasing of his breath. Then his nephew clasped the dead man's Bible to his heart, and said, "His God shall be my God, and his faith my faith!"' "Cowper's nephew, John Johnson, told the story to William Marsh, afterwards Dr. Marsh of Beckenham. Dr. Marsh told the story to his daughter, Miss Catharine Marsh, who, at the age of 88, still (in 1906) lives at Brandon. This lady related it to Dr. Moule (Theology in the English Poets; Cowper, Coleridge, Wordsworth & Burns pg 54)."
I have read the same account, from what I originally thought was one of Newton’s letters (though I have been unable to verify that) on this specific observation; and it seems to me that it was esteemed to be a notion of Cowper’s nephew, rather than some other-worldly cipher on the face of Cowper, that should be taken as evidence (though I wished it was). The previous account may bear the marks of some embellishment. It is also 106 years after his passing.
Here is evidence that Cowper was in the same condition he suffered from most of his adult life, just months before he passed (all of the information relevant to your interest begins around page 300 [Taylor’s: The Life of William Cowper]).
“It became evident towards the close of 1799, that his [Cowper's] bodily strength was rapidly declining, though his mental powers, notwithstanding the unmitigated severity of his depression, remained unimpaired.”
From: Mr. Hayley who was a longtime friend of Cowper:
“The sight of such writing from my long-silent friend, inspired me with a lively, but too sanguine hope, that I might see him once more restored.
Alas! the verses which I surveyed, as a delightful omen of future letters from a correspondent, so inexpressibly dear to me, proved the last effort of his pen."
This was dated the 31st of January; though I always thought that the last work of Cowper was
Obscurest night involved the sky,
The Atlantic billows roared,
When such a destined wretch as I,
Washed headlong from on board,
Of friends, of hope, of all bereft,
His floating home for ever left.
No braver chief could Albion boast
Than he with whom he went,
Nor ever ship left Albion's coast,
With warmer wishes sent.
He loved them both, but both in vain,
Nor him beheld, nor her again.
Not long beneath the whelming brine,
Expert to swim, he lay;
Nor soon he felt his strength decline,
Or courage die away;
But waged with death a lasting strife,
Supported by despair of life.
He shouted: nor his friends had failed
To check the vessel's course,
But so the furious blast prevailed,
That, pitiless perforce,
They left their outcast mate behind,
And scudded still before the wind.
Some succour yet they could afford;
And, such as storms allow,
The cask, the coop, the floated cord,
Delayed not to bestow.
But he (they knew) nor ship, nor shore,
Whate'er they gave, should visit more.
Nor, cruel as it seemed, could he
Their haste himself condemn,
Aware that flight, in such a sea,
Alone could rescue them;
Yet bitter felt it still to die
Deserted, and his friends so nigh.
He long survives, who lives an hour
In ocean, self-upheld;
And so long he, with unspent power,
His destiny repelled;
And ever, as the minutes flew,
Entreated help, or cried - Adieu!
At length, his transient respite past,
His comrades, who before
Had heard his voice in every blast,
Could catch the sound no more.
For then, by toil subdued, he drank
The stifling wave, and then he sank.
No poet wept him: but the page
Of narrative sincere,
That tells his name, his worth, his age,
Is wet with Anson's tear.
And tears by bards or heroes shed
Alike immortalize the dead.
I therefore purpose not, or dream,
Descanting on his fate,
To give the melancholy theme
A more enduring date:
But misery still delights to trace
Its semblance in another's case.
No voice divine the storm allayed,
No light propitious shone;
When, snatched from all effectual aid,
We perished, each alone:
But I beneath a rougher sea,
And whelmed in deeper gulfs than he.
That is all I have time for right now, but there are several extant collections of journals and letters from all of the near acquaintances of John Newton and William Cowper. Some of them may even have more information on the personal insights of Cowper’s near acquaintances (I have not nearly read them all).
Since this is not a real answer, I feel the liberty to express my own opinion about Cowper’s dying belief, and the hope he represents to all sincere believers who suffer from depression, and false fears of abandonment.
1 John 3:20
“For if our heart condemn us, God is greater than our heart, and knoweth all things.”
My version of "The Life of William Cowper" is electronic.
I don't remember where I got it from.
Sorry I didn't have more time to look into this. I do enjoy visiting Olney.